Re: Valuelist with above issue - Qlik Community

Kushal_Chawda · ‎2013-07-22

Dear All,

I am facing the issue while calculating expression using valulist & above function.

I have pivot table

1st dimension : ValueList('30+ AR Delinquency','60+ AR Delinquency')

2nd dimension : Month

Expression :

// .................................30+ Bucket

if(ValueList('30+ AR Delinquency','60+ AR Delinquency')='30+ AR Delinquency',

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),3),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'60-89'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),4),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'90-119'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),5),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'120-149'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),6),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'150-179'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),7),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'180-209'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),8),Daily_Sales_Fin_Month_Name),'##00.00'))

),

// .................................60+ Bucket

if(ValueList('30+ AR Delinquency','60+ AR Delinquency')='60+ AR Delinquency',

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'60-89'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),4),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'90-119'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),5),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'120-149'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),6),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'150-179'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'>}BookSize_Debtor_AR_POS)/vCr_Denom),7),Daily_Sales_Fin_Month_Name),'##00.00'))

)

+

(

rangesum(num(aggr((sum({<BookSize_Debtor_AR_DPD_Bucket ={'180-209'}>}BookSize_Debtor_AR_POS)/vCr_Denom),Daily_Sales_Fin_Month_Name),'##00.00')/

num(aggr(above((sum({<BookSize_Debtor_AR_DPD_Bucket ={'30-59','60-89','90-119','120-149','150-179','180-209','210+'}>}BookSize_Debtor_AR_POS)/vCr_Denom),8),Daily_Sales_Fin_Month_Name),'##00.00'))

)))

When I am using this expression I am getting the values for 30+ AR Delinquency but not for the 60+ AR Delinquency

but when i am taking single expression for 60+ AR Delinquency I am getting the values..

As I am combining valuelist condition I am not getting the values for 60+ AR Delinquency

I am not getting what is wrong with expression

a_mullick · ‎2013-07-22

Hi,

Have you tried removing the second condition:

// .................................60+ Bucket

if(ValueList('30+ AR Delinquency','60+ AR Delinquency')='60+ AR Delinquency',

As you've already tested for 30+, then the 'else' must fall through to this section anway, so the test for 60+ becomes redundant.

Thanks,

Azam

Kushal_Chawda · ‎2013-07-22

Hi

there is diffrent logic for 30+ and 60+.. so i cant remove anyone of them

a_mullick · ‎2013-07-22

Hi,

I just tried a simple example using just the LISTVALUE() function and it makes no difference to the outcome, but what I meant was that your logic is basically:

IF 30+

THEN PROCESS_30+

ELSEIF 60+ THEN PROCESS_60+

ENDIF

but if you only have 2 possible values, then this is logically equivalent to:

IF 30+

THEN PROCESS_30+

ELSE PROCESS_60+

ENDIF

there is no need to test for 60+ in the second half before processing those values.

Hope you find a solution...

Azam