Water Cooler

[Peat_Sift]

Hello I'm New Joinner. I  need your help.

     In YTD2022 column,Can you advice me the other way to write the script .Please check this code

Pick(Match($(vCalx),1,2,3),Num($(vSumPY)/1000000,'#,##0.00'),Num($(vSumPY)/1000,'#,##'),Num($(vSumPY),#,##0.00'))

Thank you So much.

[abhijitnalekar · Accepted solution]

Hi @Peat_Sift ,

Is there any specific reason why you are looking for a different method?

Because you have written an expression in an identical way and this is the best practice which qlik suggets.

Otherwise you can use the if else like below

if($(vCalx)=1,Num($(vSumPY)/1000000,'#,##0.00'),

  if($(vCalx)=2,Num($(vSumPY)/1000,'#,##'),

if($(vCalx)=3,Num($(vSumPY),#,##0.00')

)))

[Peat_Sift]

Hi @abhijitnalekar ,

      Thank you so much for your answer.I looking for new method because I received the question from my customer about how to reduce the script  but I'm new joinner on Qlik Sense .

Thank you so much again 

Best Regards,

Peat

[abhijitnalekar]

Hi @Peat_Sift,

you either create these three columns at the script level or you can create three different variables and call them accordingly to the expression.

[marcus_sommer · Accepted solution]

To reduce the lengths of the expression you may just use shorter variable-names and maybe also using variables for the divisor and the formatting. But I wouldn't suggest such an approach because the administrative overhead increased with many more variables and the readability goes into the direction of a nightmare ...

Nevertheless this expression could be improved, for example by skipping the match() part which wouldn't be necessary by variable-values of 1,2,3 and the pick-approach could be used directly for the divisor and the formatting. This means this expression might be look like:

Num($(vSumPY) / Pick($(vCalx),1000000,1000,1), Pick($(vCalx),'#,##0.00','#,##',#,##0.00'))

which isn't just shorter else it would be also more performant because the most heavy calculation which is $(vSumPY)  would be only performed ones and not three times.

Further it looked as if vCalx is a global variable and not an dimensionally-depending expression and so the logic might be outsourced in own variables and so in the end it may result in:

Num($(vSumPY) / $(vDiv), '$(vFormat)')

- Marcus