Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All. Posting on behalf of a customer to see if anyone can kindly assist. If not - no worries - I can attempt to find a Qlik resource to advise, and would of course paste the answer here ! Thanks Andrew.
I have the following expression which will switch the data show from stock qty / percentage to stock value / percentage.
If I use the auto formatting option it shows the data as xx.xxM or xxxK etc for the value or xx.xx% for percentage. I want to show the Currency symbol when used for value (€)
The below works when I use (measure expression but there doesn’t seem to be a format option to show the Stock Value in the same format as “Auto” but with a currency symbol.
If(V_qtyvscost='Cost',
if(v_Valvsper='Value',
Sum({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}[Stock Value €]),
num(Sum({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}[Stock Value €])/Sum({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}TOTAL[Stock Value €]),'######.00%')),
if(v_Valvsper='Value',
Count({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}[Item_ID]),
num(Count({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}[Item_ID])/Count({<[Reporting Date]={'$(=monthend(MAX([Reporting Date]),$(=v_Moncom)))'}>}TOTAL[Item_ID]),'######.00%')))
The proposed solution to this is the below
=If(sum([Stock Value €])/1000000 > 1,Num(sum([Stock Value €])/1000000,'€ #,### M'),
If(sum([Stock Value €])/1000, Num(sum([Stock Value €])/1000,' € #,### k'),
Num(sum([Stock Value €]),' € #.##0;€ -#.##0') ))
But this gives off results on charts when for example 100M compared to 100K would be the same height for a bar.