Nested IF Optimization

[Lakshminarayanan_J]

Dear Experts,

Need to optimize the below query , It has been used in across pie chart, Adhoc table, Bar chart.

We have  2 different data which will be displayed in absolute (#) and percentage(%)

1.No

2.J

Expression need to optimize

if(Calc='Auto',

if(PivotContent=No(#)', $(VAuto#) ,if(PivotContent='No(%)', $(VAuto%))),

if(CalcMeasure='Autoride’,

if(PivotContent=' No(#)'', $(VAutoride#) ,if(PivotContent= ’No(%)', $(VAutoride%))),

if(CalcMeasure='Manual',

if(PivotContent=' No(#)'',$(VManual#) ,if(PivotContent=' No(%)'',$(VManual%))),

if(CalcMeasure='Fullycost',

if(PivotContent='J(#)',$(VFullycost#) ,if(PivotContent='J(%)',$(VFullycost%))),

if(CalcMeasure='AutoAdjust',

if(PivotContent='J(#)',$(VAutoAdjust#) ,if(PivotContent='J(%)',$(VAutoAdjust%))),

if(CalcMeasure='PAutocost',

if(PivotContent='J(#)',$(VAutocost#),if(PivotContent='J(%)',$(VPAutocost%))),

if(CalcMeasure='Ncost',

if(PivotContent='J(#)',$(VNcost#) ,if(PivotContent='J(%)',$(VNcost%)))

)))))))

@rubenmarin  @Frank_S@marcus_sommer  @Lech_Miszkiewicz  @Arlind 

Qlik Sense Business 

[rubenmarin]

Hi, Why Auto is 'Calc' and the others 'CalcMeasure', just a typo?

If CalcMeasure it's something selected outside, and with only one values selected, and PivotContent only has those 2 possible values, the expression can be simplified as:

if(PivotContent=No(#)'
  ,$(V$(=CalcMeasure)#) 
  ,$(V$(=CalcMeasure)%)
)

[Lech_Miszkiewicz]

Gi @Lakshminarayanan_J 

It is not clear which parts of this expressions can be moved to smart design of data model and which must be hardcoded. Looking at labels of your measure the concept seems to be similar to the one you would normally use in PnL where groupings can be done in data model layer. That would be typical to tabular view. Is this something you could use? To answer that question we would actually have to understand each of the variables components you have inside of your IF statement. We would need to understand

• what is the go with # and % - is this something what is shown in the same view at the same time for different dimension values or is it always showing only # or only % at the time/
• why do you have so many expressions for Autoride, Manual,Autocost etc.. are those additive measures
• what are dimensions you are putting those measures agains? Could dimensions be used to actually have a single measure instead of many?

I am just guessing here - without knowing data model, requirements of how the finished view needs to look like it is pointless to guess i reckon. Maybe others will help more.