19 Replies Latest reply: Dec 18, 2017 7:19 AM by vinayak lAKESHRI Branched from an earlier discussion. RSS

    Re: How can add "If and" condition in pivot table in Qliksense

    vinayak lAKESHRI

      4th formula

      If (Client_type=Individual and country = 'A', (Sum(VALUES)-(value))/sum(VALUES),

      If (Client_type=Individual and country = 'B', (Sum(VALUES)-(value))/sum(VALUES),

      NULL()))

       

      I used above formula in pivot table but I am getting negative values in % column, that is not correct. somebody help me to fix that issue ..I want to  fix this issue in the next 48 hours.

      Awiting Solution on  this error..

      Thanks in advance for quick response

        • Re: How can add "If and" condition in pivot table in Qliksense
          Sunny Talwar

          Would you be able to share a sample to visualize your issue?

          • Re: How can add "If and" condition in pivot table in Qliksense
            vinayak lAKESHRI

            I tell you from starting..

             

            I have uploaded data for below three tables , I want % of calculation for all country which will reflect in the

            % column of pivot table.

             

            Calculation formula is

             

            (Total_SGH - Database_SGH) / (Total_SGH

             

             

            Used in measure :

             

            1st:

            if([Client Type] ='Individual' or COUNTRY = 'SGH', (sum(Values) - (Value))/(sum({1}Values)),

            if([Client Type] ='Individual' or COUNTRY = SGHJ',(sum(SGHJValues) - (Value))/(sum({1}SGHJValues)),

            if([Client Type] ='Individual' or COUNTRY = 'ARG',(sum(ARGValues) - (Value))/(sum({1}ARGValues)),

            Null())))

             

             

             

             

            2nd:

            if([Client Type] ='Individual' AND COUNTRY = 'SGH', (sum(Values) - (Value))/(sum({1}Values)),

            if([Client Type] ='Individual' AND COUNTRY = SGHJ',(sum(SGHJValues) - (Value))/(sum({1}SGHJValues)),

            if([Client Type] ='Individual' AND COUNTRY = 'ARG',(sum(ARGValues) - (Value))/(sum({1}ARGValues)),

            Null())))

             

             

             

             

            I hv tried  If condition for calculation of % for three country but I am getting negative value of one of the country and wrong calculation of 2nd country and only one country calculation is getting correct.

             


            Database_SGH:
            CrossTable (Month, Value, 7)


            LOAD
                "Data Elements",
                "FCC Top 20",
                "Client Type",
                "DQ Dimension",
                COUNTRY,
                CLIENT_ROLE,
                DE_KEY,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
                "12/31/2017"
               
            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
            (ooxml, embedded labels, table is Database_SGH);

             


            Total_SGH:
            CrossTable (Month, Values, 1)

            LOAD
                "Client Type" as SGH_Client_Type ,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
               "12/31/2017"
              
            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
            (ooxml, embedded labels, table is Total_SGH);

             

             

             


            Database_SGHJ:
            CrossTable (Month, Value, 7)

            LOAD
                "Data Elements",
                "FCC Top 20",
                "Client Type",
                "DQ Dimension",
                 COUNTRY,
                CLIENT_ROLE,
                DE_KEY ,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
                "12/31/2017"

              

            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]

            (ooxml, embedded labels, table is [Database-SGHJ]);

             

            Total_SGHJ:
            CrossTable (Month, SGHJValues, 1)

            LOAD
                "Client Type"as SGHJ_CLient_Type,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
                "12/31/2017"

            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
            (ooxml, embedded labels, table is Total_SGHJ);

             

             

            Database_ARG:
            CrossTable (Month, Value, 7)

            LOAD
                "Data Elements",
                "FCC Top 20",
                "Client Type",
                "DQ Dimension",
                 COUNTRY,
                CLIENT_ROLE,
                DE_KEY,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
                "12/31/2017"
              
            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
            (ooxml, embedded labels, table is [Database-ARG]);

             

            Total_ARG:
            CrossTable (Month, ARGValues, 1)

            LOAD
               "Client Type" as ARG_Client_Type,
                "12/31/2016",
                "1/31/2017",
                "2/28/2017",
                "3/31/2017",
                "4/30/2017",
                "5/31/2017",
                "6/30/2017",
                "7/31/2017",
                "8/31/2017",
                "9/30/2017",
                "10/31/2017",
                "11/30/2017",
                "12/31/2017"
               
            FROM [lib://AttachedFiles/Data Profiling Template.xlsx]
            (ooxml, embedded labels, table is Total_ARG);

             

            • Re: How can add "If and" condition in pivot table in Qliksense
              vinayak lAKESHRI

              anybody have any other option for calculation which will help to get correct % calculation, please share other function or  condition which can use to fulfill the correct calculation requirement.