Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
Hi Rohit,
Please send me the application,i didn't find any attachment.
Regards
Kiran
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.
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.
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
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
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