Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

Active Contributor
0 Kudos

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.