topic Re: Custom Number Format QlikView - 'Bn','MM','K(000)','Actuals' in App Development

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

jyoshna — Sun, 22 Jan 2023 12:24:59 GMT

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?

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

barnabyd — Sun, 22 Jan 2023 20:56:41 GMT

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.

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

jyoshna — Mon, 23 Jan 2023 09:51:29 GMT

@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 ?

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

barnabyd — Mon, 23 Jan 2023 21:02:27 GMT

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.

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

jyoshna — Tue, 31 Jan 2023 08:47:08 GMT

@barnabyd

The below expression is perfectly working for my scenario.

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