Skip to Content
avatar image
Former Member

Filter a WEBI table based on Max(sting-value)

Hi experts,

I'm having some issues filtering a table on a dimension called Time:FY_FW.

The dimension contains the following values (data type string):

- FY17 WK30

- FY17 WK31

- FY17 WK32

- FY17 WK33

- FY17 WK34

The report should contain only the records with a max value (in this example FY17 WK34). I've tried the following:

- create variable =Max(Time:FY_FW) (BO will create a measure of this variable)

- create variable =If(Time:FY_FW = Max(Time:FY_FW);1;0)

- create variable in which I substring the FY and FW and subsequently took the Max value of it.

Unfortunately, none of the above seems to work as it continues to return al FY_FW records. Could anyone point me in the right direction?

Kind regards,

Chris Raes

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Dec 29, 2016 at 07:22 PM

    try something like this.

    Var=if(tonumber(right([Time:FY_FW];2);"##"))=max(tonumber(right([Time:FY_FW];2);"##")) in report then 1 else 0

    Drag Var variable in the table and see if values are correct or not.if yes then Apply filter on Var variable where value equal to 1.

    MAX or MIN functions will work only with number or date data type above formula i have first extracted the right part and converted to the number.

    Add comment
    10|10000 characters needed characters exceeded