IF statements and set analysis

[khaycock]

I'm trying to get a points system running but I want it to only give points if the person is in the certain area.

I know this will include set analysis and the IF statement but can't get it to give the right results as I'm not sure exactly where the set analysis will go.

This is the IF statement:

IF(sum(Bins)>=300, 50, IF(sum(Bins)>=250, 40, IF(sum(Bins)>=200, 30, IF(sum(Bins)>=150,20, IF(sum(Bins)>=100, 10, 10)))))

And I want to make it so it only shows where AreaName = Materials and the other Area Names will have 0 points.

Which order do I need to make the expression?

Thanks

[adamdavi3s · Accepted solution]

PICK(MATCH(AreaName,'Materials')+1,0,IF(sum(Bins)>=300, 50, IF(sum(Bins)>=250, 40, IF(sum(Bins)>=200, 30, IF(sum(Bins)>=150,20, IF(sum(Bins)>=100, 10, 10))))) )

This is written on the fly so apologies if it doesn't work first off!

Match will return 0 (if no match) or 1 (if Materials is the AreaName)

The Pick then 'picks' the item from the pick list based on this result, however Pick is 1 based so we need to add one to the result of the match.

So, if the AreaName is not equal to 'Materials' the pick formula will return list item 1 (0+1) which is 0

If it is materials then the pick formula will return list item 2 (1+1) which is your IF formula

[sunny_talwar]

May be this

If(AreaName = 'Materials',

If(sum(Bins)>=300, 50,

If(sum(Bins)>=250, 40,

If(sum(Bins)>=200, 30,

If(sum(Bins)>=150, 20,

If(sum(Bins)>=100, 10, 10))))), 0)

[khaycock]

Spot on, thank you!!

[Anil_Babu_Samineni]

Try something like this

IF(sum({<AreaName = {'Materials'} and AreaName = $::P(AreaName = {"$(=Len(AreaName) = 0)"})>} Bins)>=300, 50, IF(sum({<AreaName = {'Materials'} and AreaName = $::P(AreaName = {"$(=Len(AreaName) = 0)"})>} Bins)>=250, 40, IF({<AreaName = {'Materials'} and AreaName = $::P(AreaName = {"$(=Len(AreaName) = 0)"})>} Bins)>=200, 30, IF(sum({<AreaName = {'Materials'} and AreaName = $::P(AreaName = {"$(=Len(AreaName) = 0)"})>} Bins)>=150,20, IF(sum({<AreaName = {'Materials'} and AreaName = $::P(AreaName = {"$(=Len(AreaName) = 0)"})>} Bins)>=100, 10, 10)))))

[adamdavi3s]

Not a problem at all, Pick(Match( is a very handy combination and in larger apps it out performs IF by a large margin

[sunny_talwar]

adamdavi3s‌ - although that is what I have been made to think also that Pick(Match()) offers a drastic improvement over if statement. But did you ever get a chance to test it out? My intentions are not to say that my expression is better than yours, but recently I switched an if statement with pick match in a relatively bigger application (4GB) hoping to get some better response time. Based on multiple tests, I found out that if was performing slight better than the Pick(Match()). The only good thing was the simplicity of the expression.

Have you ever tested this or is this based on what you have heard and seen?

[adamdavi3s]

Based on my experience yes it is a lot faster, this does however go back to version 10 and I haven't re-tested it since moving to 12.

We had a chart which would not calculate with an IF, with pick(match( it absolutely flies. The dashboard is probably running to about 3gb but the formula is quite intensive with several nested IF

This change was made on the basis of advice from a Qlik Consultant we spent a day with and we haven't looked back since.

Based on this real world experience I would always advocate the use of pick match in larger applications especially where there are a number of nested IFs

[sunny_talwar]

Not sure why I did not see any performance benefits. Will try to do some more digging when I will get some time.

Thanks,

Sunny