Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Team,
Inline_MillionsDivision:
Load * Inline [
NumbersFormat
Bn
MM
K(000)
Actuals
];
Sum({<Year = {$(vMaxYear)>} [Sales Credits])/$(vNumberDivision)
pick(match(GetFieldSelections(NumbersFormat),'Bn','MM','K(000)','Actuals'),10000000,1000000,1000,1)
IN Front end I have created a list box which displays Above created Inline Number Formats
Now any option I choose i am getting decimal places, My Requirement is now to display decimal values for other Number (Bn,MM,K(000)) Formats except Actuals when we input our selection in list box.
Can someone provide me a solution for this?
I think this might work ...
Set a new variable vNumberFormat :
pick( match( GetFieldSelections(NumbersFormat)
, 'Bn', 'MM', 'K(000)', 'Actuals' )
, '#0', '#0', '#0', '#0.00'
)
Then your expression becomes:
num( Sum( {<Year = {$(vMaxYear)>} [Sales Credits]) / $(vNumberDivision)
, $(vNumberFormat)
)
... but that's a lot of code for a simple sum() expression!
Cheers, Barnaby.
HI Barnaby,
Thank You for response.
Actually, for quick understanding purpose i gave Sum() but actually what i am using is below.
Sum({<Year = {$(vMaxYear)},Month=,[Fee Date]={'>=$(=(vMinDate))<=$(=(vMaxDate))'},LostDoneStatus={'DONE'}>}([Net Revenue]+[Sales Credits]))/$(vNumberDivision)
The above expression i am storing into another variable (vCY_YTD) and i used it in Pivot chart
So my pivot chart expression has $(vCY_YTD) , likewise i have calculated for prior years also.
With the approach you suggested above now i have modified the expression to
=num($(vCY_YTD),$(vNumberFormat))
But still it didn't give me the desired output, decimals are being displayed for all the selections.
Can you further help me ?
G'day @jyoshna,
With a situation like this, I would create a simple stand-alone app and test out each concept in isolation. I just did a quick google search on "qlik numbers with scaling factor" and found this article where the 'only()' function fixed the problem.
Cheers, Barnaby.
The below expression is perfectly working for my scenario.
= MONEY($(vCY_YTD),if(GetFieldSelections(NumbersFormat)='Actuals','$#,##0;($#,##0)',
'$#,##0.00;($#,##0.00)'))