7 Replies Latest reply: Oct 6, 2016 10:37 AM by Rohit Kumar RSS

    How to change number formatting based on the filter selection

    kiran kumar

      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

        • Re: How to change number formatting based on the filter selection
          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

          • Re: How to change number formatting based on the filter selection
            Shraddha Gajare

            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.

              • Re: How to change number formatting based on the filter selection
                kiran kumar

                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

              • Re: How to change number formatting based on the filter selection
                kiran kumar

                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

                Thousand.JPG

                 

                 

                if selected Million

                Million.JPG

                 

                if i selected Billion

                Billion.JPG

                 

                IF i selected Trillion

                Trillion.JPG

                 

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

                 

                Regards

                Kiran

                  • Re: How to change number formatting based on the filter selection
                    Rohit Kumar

                    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