IF Statement in Pivot Table

[Jeff_Brown]

Hello,

I'm new to Qlik and trying to apply an IF statement in an expression that applies a certain percentage to the sales budget for given customers (head office codes).

Whilst the IF statement itself works, it doesn't work in pivot tables unless i have the head office code as a dimension. i'm assuming i need an Aggr function or something similar...but tried everything and can't get it to work.

My expression is currently written as;

If([Head Office Code]='1AAA',Sum([#SalesBudget])*.03,
If([Head Office Code]='1BBB',Sum([#SalesBudget])*.0225,
If([Head Office Code]='1CCC',Sum([#SalesBudget])*.05,
If([Head Office Code]='1DDD',Sum([#SalesBudget])*.02,
If([Head Office Code]='2EEE',Sum([#SalesBudget])*.0475,
If([Head Office Code]='9FFF',Sum([#SalesBudget])*.03,
If([Head Office Code]='9GGG',Sum([#SalesBudget])*.01,
0)))))))

Please help...

Thanks,

Jeff

[KristinaH]

Hi 🙂

Are these percentages fixed ? Are you applying it only for these head offices ?

The best way would be to map the % for each headoffice in script and then use it to pre-calculate budget for settlement. 

Kristina

[lironbaram]

hi 

assuming you have in your pivot table two dimensions : Dim1,Dim2 that are not head office 

than something in the line of 

sum(aggr(If([Head Office Code]='1AAA',Sum([#SalesBudget])*.03,
If([Head Office Code]='1BBB',Sum([#SalesBudget])*.0225,
If([Head Office Code]='1CCC',Sum([#SalesBudget])*.05,
If([Head Office Code]='1DDD',Sum([#SalesBudget])*.02,
If([Head Office Code]='2EEE',Sum([#SalesBudget])*.0475,
If([Head Office Code]='9FFF',Sum([#SalesBudget])*.03,
If([Head Office Code]='9GGG',Sum([#SalesBudget])*.01,
0))))))),[Head Office Code],Dim2,Dim1)

should do the trick , the thing with aggr function , is it disregard the table dimensions so , you need to include all your dimensions in the expression , so what i would do is during the script add the multiplier to the raw data as additional column than your expression will by that much simpler , Sum([#SalesBudget]*multiplier )

[jonathandienst · Accepted solution]

An alternative to a hard-coded nested If statement (which is hard to maintain or update), I would look to a data-driven solution using a table (inline loaded here, but could come from another source), and applymap() the factor into the table.

Before loading the fact table:

MAP_MARKUP:
Mapping LOAD * 
Inline
[
	HO, Escalation
	1AAA, 0.03
	1BBB, 0.0225
	1CCC, 0.05
	1DDD, 0.02
	2EEE, 0.0475
	9FFF, 0.03
	9GGG, 0.01
];

Then during the fact or dimension load:

LOAD ...
	[Head Office Code],
	ApplyMap('MAP_MARKUP', [Head Office Code], 0) as Escalation,
	...

After that, your base expression becomes simply:

Sum([#SalesBudget] * Escalation)

If you want to see sum of rows in the pivot, you will still need an Aggr():

Sum(Aggr(Sum([#SalesBudget] * Escalation), [Head Office Code], Dim1, Dim2)

[Head Office Code] may not be needed. Dim1 / Dim2 are the pivot table dimensions - you need all of them.

[Sue_Macaluso]

@Jeff_Brown Hi Jeff, I would like to move this into the correct product forum. This one is suppose to be for non product discussions. If you let me know if your using QlikSense or View I will move it. Thanks

[Jeff_Brown]

QlikSence thanks Sue

[Jeff_Brown]

Thank you all your feedback...the best advise was to create in the load script and make the expression in the charts much simpler.