topic Limit value in pivot table according to variable? in QlikView

Limit value in pivot table according to variable?

Wed, 25 Nov 2020 16:16:04 GMT

Dear All,

I have read many topic related to limit value, however, there is no precise answer for my case.

I attach the qlik file for easier view.

Please help

Many thanks

Dong

Anil_Babu_Samineni — Fri, 14 Oct 2016 06:34:25 GMT

What do you mean Limit Value, Can i know more input issues from your end, Bcz i don't have license

Anonymous — Fri, 14 Oct 2016 06:44:19 GMT

Hi Dong,

Create a variable called vRDL_Dim with this expression:

=If(vRSL='All','>-9999',vRSL)

Change batch dim with a calculated dim with this expression:

=IF(([Expiry Date]-vDate)/30$(vRSL_Dim), Batch)

Select Suppress When Value Is Null on this dimension.

Regards!!

Fri, 14 Oct 2016 07:17:23 GMT

I have 3 dimensions: description, Batch, Expiry Date; and 3 expressions are RSL, RSL text, Qty where:

* RSL = ([Expiry Date]-vDate)/30 (vDate is variable which value is given by Calendar object)

* RSL text = if(RSL<=6,'<=6',if(RSL<=9,'<=9',if(RSL<=12,'<=12','>12')))

* Qty = sum([Stock Total])

vRSL is variable which has value from input box. It is constrained by listed value with formula =Concat(DISTINCT Shelflife,',')

And Shelflife is create by below scripts

LOAD * INLINE [
Shelflife
All
>12
<=12
<=9
<=6
];

And in "Show value" under RSL text expression, I put the formula [RSL text]=vRSL

but it doesn't work when I choose any value in input box whether it is <=6, <=9, <=12, >12, or All.

The idea is that the pivot table will show only records matching between RSL text and Shelflife

Fri, 14 Oct 2016 07:18:13 GMT

Thanks so much