Difference in behavior of numeric-format between pick-match and if-statement

[andy]

Hi,

Having two expressions doing the same one using pick-match and the other if.

I cannot explain why this two expressions differ in the way the y-axis is formatted.

When using pick-match any chart in Qlik Sense cloud or on windows Nov 2022 SR1 is stuck in the first available num-format given in the expression.

The calculation do change when selecting different alternatives

Here is the script. App is uploaded as well

Adding the expressions directly in the charts gives same behavior.

Any suggestions on what is happening?

DummyData:
Load * Inline [
value
0.42
];

Set vShowExprLabel = '42%'; //Default value
Set vShowExprValues = 42%|4.20; 

//this expression will swap between the expressions but always have an y-axis set to %
Set vExpr1 = 
Pick(Match(vShowExprLabel,'42%','4.20')	
    ,num(sum(value),'0%')
    ,num(sum(value*10),'# ##0,00')
);

//This will work as expected
Set vExpr3 = 
if(vShowExprLabel='42%'	
    ,num(sum(value),'0%')
    ,num(sum(value*10),'# ##0,00')   
);

[marcus_sommer]

What happens if you reverse the if-query to:

Set vExpr3 = 
if(vShowExprLabel='4.20'	
    ,num(sum(value*10),'# ##0,00')   
    ,num(sum(value),'0%')
);

I could imagine that the if-loop behaved now like the pick(match()). If so it means that '4.20' isn't identically to your calling-value, for example because this value is a number and your query expects a string.

Even if both values (querying + calling) are numeric a comparing of a binary-number against a decimal-number could fail because a binary system hasn't always an equivalent value. You may try a check against the number by showing more digits. It may result in something like: 4.1999999999999 instead of 4.2.

[andy]

Hi,

Before I  hade two strings Exp1 and Exp2 and that gave the same behavior and the condition does work since the value does change with a factor 10. It is only the num-format that doesn't change when doing it with pick-match. So the pick-match is picking the correct expression.

[jbhappysocks]

If your vExpr1 is used in a straight table it works, and if the expression is split into 2 picks, one for value and one for format it also works.

Set vExpr2 =
num(
Pick(Match(vShowExprLabel,'42%','4.20')
,sum(value*10)
,sum(value)),

Pick(Match(vShowExprLabel,'42%','4.20')
,'# ##0,00'
,'0%')
);

Looks like the bar chart is the bad guy here.

[marcus_sommer]

Ok. this looks really strange ... Before playing further I suggest to look again to the object-properties if they are really identically and/or swapping the expression between them and/or creating a complete new chart from the scratch - just to exclude an object-corruption or having overseen any setting.

If the issue remained you may extend your logic to an appropriate rounding-function, like:

round(sum(value), 0.01)

to prevent the above mentioned binary-system-stuff.

Another thought goes to num() which is here applied with only one parameter which in my experience only worked if the used thousand + decimal delimiter are identically with the default-settings. If not the second and third parameter of num() needs to specified, too.

[andy]

Hi,

I also thought it might be bad luck with an object so I created the basic app that I attached to the first post from scratch with the same issue. My real app has the issue within a line-chart.

Regarding your binary-stuff suggestion I think you are missing that the condition is working, it does select the appropriate part, it is just the formatting that doesn't have effect in the bar-, combo- or line-chart but it works correct when used in a straight table.

I now tried adding more parameters to the num() but with same result.

I'll add a support-case

[marcus_sommer]

I think you are right by regarding the issue as a bug and creating a support-case for it because IMO the evaluation  of such expression should be equally regardless if they is called directly respectively within a cell or within any other meta-data layer like the axis.

Nevertheless I tried to give hints to find a workaround assuming possible causes for such a different behaviour ... and in this regard you may play a bit more with it and simplifying your approach with something like: if(1 = 1, num(0.5), num(0.5, '0.0%')) or similar ... maybe also combining num(num#()) and/or setting min/max/steps for axis and other settings ...

[jbhappysocks]

I changed the condition to 

Set vShowExprLabel = 'percentage'; //Default value
Set vShowExprValues = decimal|percentage;

To avoid any suspicion this has anything to do with numbers in the if-statement.

I also added a 4:th expression that changed the order in the first pick, so decimal comes before percentage.

Percentage selected: All expressions correct in the straight table. All bar charts shows correct values, but the last added expression where decimal comes first in the pick statement shows incorrect format.

Decimal selected: All expressions correct in the straight table. All bar charts shows correct values, but the first added expression where percentage comes first in the pick statement shows incorrect format.

The expression where the pick is split in two (vExpr2) shows expected outcome for both Decimal and Percentage.

Even if workarounds with num#() might exist it shouldn't be needed here. This just look wrong.

[andy]

Got this from support

"bug ID QB-18156"

[Chetan_MN · Accepted solution]

Issue resolved in Qlik Sense SaaS, please refer to below release notes

https://community.qlik.com/t5/Release-Notes/Qlik-Cloud-Release-Notes-March-2023/ta-p/2043333

For Qlik Sense Enterprise on Windows, the issue will be solved in next major release