Custom Number Format QlikView - 'Bn','MM','K(000)','Actuals'

[jyoshna]

Hi Team,

• I have created Inline Load for my Desired Number Format

Inline_MillionsDivision:
Load * Inline  [
NumbersFormat
Bn
MM
K(000)
Actuals
];

• Created a Pivot Chart with desired Dimension and my expression displays

Sum({<Year = {$(vMaxYear)>} [Sales Credits])/$(vNumberDivision)

• vNumberDivision Variable has below

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.

• Current Number Format which i have chosen under Chart Properties is Money ($#,##0.00;($#,##0.00))

Can someone provide me a solution for this?

[barnabyd]

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.

[jyoshna]

@barnabyd 

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 ?

[barnabyd]

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.

[jyoshna]

@barnabyd 

The below expression is perfectly working for my scenario. 

= MONEY($(vCY_YTD),if(GetFieldSelections(NumbersFormat)='Actuals','$#,##0;($#,##0)',
'$#,##0.00;($#,##0.00)'))