Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
Partner
Partner

Aggr with If Expression

Hi All,

Can any body help me to debug the issue in this expression :

pick(_order_bucket,


sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,


sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,


sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion


)


I m trying to add it in Straight table to show 3 row in Straight table but only first one is showing number rest is showing Zero.


but when i write this each ode individiually than working fine in Text box.


stalwar1


Regards,

Vanraj

1 Solution

Accepted Solutions

Re: Aggr with If Expression

Is _Order_bucket an island table? try this

Pick(Only({1}_order_bucket),

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion

)

View solution in original post

5 Replies

Re: Aggr with If Expression

Is _Order_bucket an island table? try this

Pick(Only({1}_order_bucket),

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  > vEGRevBucket2,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  <= vEGRevBucket2 and

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  >= vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion,

sum(Aggr(if(sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income])  < vEGRevBucket1,

sum({<Year = {'$(v_this_year)'},Year_month=,Month=,Year_month= {'<=$(v_max_date)'},[Latest Coverage Tagging] = {'$(vCoverageChart)'},[Latest Industry] -={'FI','NGI'}>}[Total Income]),0),[Latest EG Name])) / vMillion

)

View solution in original post

Partner
Partner

Re: Aggr with If Expression

Thanks a lot Sunny for quick reply.

Partner
Partner

Re: Aggr with If Expression

Will this work for Qlik Sense?

I'm Trying to do the same, but the third "record" always return 0 even if the formula is the same.

I have the same issue using "IF" . The Else, never works.

image1.pngimage2.png

Re: Aggr with If Expression

May be try adding NODISTINCT after Aggr() in your expression and see if that helps

Sum(Aggr(NODISTINCT .....

Partner
Partner

Re: Aggr with If Expression

That fixed it! Thank you