Skip to Content
0

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

Dec 29, 2016 at 05:15 PM

65

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

AMIT KUMAR
Dec 29, 2016 at 07:22 PM
0

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 objects.in above formula i have first extracted the right part and converted to the number.

Share
10 |10000 characters needed characters left characters exceeded