How to change number formatting based on the filter selection

[Anonymous]

Hello Experts,

How to change number formatting based on the filter selection.

Ex: i have a bar chart with revenue data with number/money format , i this scenario data is showing too many numbers so my end user ask me to provide formatting filter.

Based on the filter He can select thousands or Million or Trillion etc.., Is it possible this way.

Please find the attached document for your reference.

in capture it is showing 'G', i did not understand what G is

in capture1  it is showing lengthy values , here i want shorten the values like 10 thousang,10 million,.....soon

Thanks in Advance

Regards,

Kiran

[rohitk1609 · Accepted solution]

Hi Kiran,

I have created an application for you , This will sort your problem, You may select thousand then the data in bar chart will become in thousand same for million etc.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Rohit Kumar

[Anonymous]

Hi Rohit,

Please send me the application,i didn't find any attachment.

Regards

Kiran

[rohitk1609]

Hi Kiran,

I am sorry I have deleted that application from my machine. What I have done. I created a independent table with field name Format and values like Thousand , million and billion , then I went to the app overview and created a measure where i put a condition by IF and Else as If format = thousand then amount/1000 ,if format = million then amount/100000 somehow, Please try once at your side.

[shraddha_g]

You can also have extension with variable and use that variable in measure.

For ex.

you have variable called 'vSeperator' with 3 values 1000,100000,1000000

then you can you use it as amount/$(vSeperator).

It will optimize the expression.

Sometimes using IF else in expression slow down the performance.

You can use Qlik Branch for this purpose.

I hope it is helpful.

[Anonymous]

Hi Rohit,

when iam trying to do the same logic

if(Fomart = 'Thousand', Sum(Revenue)/1000,

if (Fomart = 'Million', Sum(Revenue)/1000000,

if (Fomart = 'Billion', Sum(Revenue)/1000000000,

if (Fomart = 'Trillion', Sum(Revenue)/1000000000000,

Sum(Revenue)))))

iam getting like this: if selected Thousand

if selected Million

if i selected Billion

IF i selected Trillion

Iam getting wrong values,  how can i resolve this issue?

Regards

Kiran

[Anonymous]

Hi Shraddha ,

I have created vSeperator variable, in formula field i added like this  sum(Revenue)/$(vSeperator)

i have assigned vSeperator =

if(Fomart = 'Thousand', Sum(Revenue)/1000,if (Fomart = 'Million', Sum(Revenue)/1000000,if (Fomart = 'Billion', Sum(Revenue)/1000000000,if (Fomart = 'Trillion', Sum(Revenue)/1000000000000,Sum(Revenue)))))

but still iam not getting proper value please give me suggestion to resolve this issue.

Regards

Kiran

[rohitk1609]

Hi Kiran,

Here no option for attaching the demo app I have created for you.

So what you need to do , Create a new app and go to script editor and copy and paste the below code.

Temp:

load

*

Inline

[

Date, Att, Revenue

01-01-2016,A,10

01-01-2016,A,40

02-01-2016,B,20

03-01-2016,C,30

04-01-2016,D,100

05-01-2016,E,50

06-01-2016,F,60

];

temp2:

Load

*

Inline

[

ID,Fomart

2,Thousand

3,Million

4,Billion

];

then drag an KPI and Use your mentioned formula :

if(Fomart = 'Thousand', Sum(Revenue)/1000,

if (Fomart = 'Million', Sum(Revenue)/1000000,

if (Fomart = 'Billion', Sum(Revenue)/1000000000,

if (Fomart = 'Trillion', Sum(Revenue)/1000000000000,

Sum(Revenue)))))

then create an Format filter as you did and check once.

Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

Rohit Kumar