Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
Lakshminarayanan_J

Nested IF Optimization

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 

Lakshminarayanan J
To help users find verified answers, please don't forget to use the "Accept as Solution" button
Labels (2)
2 Replies
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
Partner Ambassador/MVP
Partner Ambassador/MVP

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.

 

cheers Lech, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful to the problem.