Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bohravanraj
Partner - Creator II
Partner - Creator II

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
sunny_talwar

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
sunny_talwar

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
Partner - Creator II
Partner - Creator II
Author

Thanks a lot Sunny for quick reply.

bernice_gonzale
Partner - Contributor II
Partner - Contributor II

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

sunny_talwar

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

Sum(Aggr(NODISTINCT .....

bernice_gonzale
Partner - Contributor II
Partner - Contributor II

That fixed it! Thank you