Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

if statement help

I have an IF statement that isnt working.  I am trying to use this if statement in my dimension. 

I want my dimension to show buckets of Sales per Agent.  I want to have categories :

0-2

3-4

5-6

7+

Sales per Agent.

=if(aggr(if(count([eAppSaleMetric])<=2, 1, 0), [Agent Name],dual('0-2',1),
if(aggr(if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0), [Agent Name], dual('3-4',2),

if(aggr(if(count([eAppSaleMetric])>=5 and count([eAppSaleMetric])<=6, 1, 0), [Agent Name, dual('5-6',3),

if(aggr(if(count([eAppSaleMetric])>=7, 1, 0), [Agent Name] ,   dual('7+',4)))))))))

Any help would be greatly appreciated.  Thanks.

3 Replies
whiteline
Master II
Master II

Hi.

You sould use aggr and ifs properly:

=aggr(if(count([eAppSaleMetric])<=2, 1, 0),

            dual('0-2',1),

            if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0),

               dual('3-4',2),

               '...'))

          ,[Agent Name])

So that you iterate with aggr over each [Agent Name] classifing it along the count([eAppSaleMetric]) value (returning the class).

Hope this helps.

upd:

=aggr(if(count([eAppSaleMetric])<=2, dual('0-2',1),

            if(count([eAppSaleMetric])<=4, dual('3-4',2), '...'))

          ,[Agent Name])

Not applicable
Author

Ok, so I corrected my Aggr IF statement and I am still having issues...any suggestions.  Thank you.

aggr(if(count([eAppSaleMetric])<=2, 1, 0), dual('0-2',1),

if(count([eAppSaleMetric])>=3 and count([eAppSaleMetric])<=4, 1, 0), dual('3-4',2),

if(count([eAppSaleMetric])>=5 and count([eAppSaleMetric])<=6, 1, 0), dual('5-6',3),

if(count([eAppSaleMetric])>=7, 1, 0), [Agent Name] ,   dual('7+',4))

whiteline
Master II
Master II

Oh... you've corrected them even better than me ))

I think this one is what you're looking for:

=aggr(if(count([eAppSaleMetric])<=2, dual('0-2',1),

       if(count([eAppSaleMetric])<=4, dual('3-4',2),

       if(count([eAppSaleMetric])<=6, dual('5-6',3), dual('7+',4)))), [Agent Name])

Sorry, didn't noticed the logic of your ifs yesterday, just mechanically corrected aggr() syntax.