Aggr with If Expression

[bohravanraj]

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

[sunny_talwar · Accepted solution]

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

)

[bohravanraj]

Thanks a lot Sunny for quick reply.

[bernice_gonzale]

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.

[sunny_talwar]

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

Sum(Aggr(NODISTINCT .....

[bernice_gonzale]

That fixed it! Thank you