9 Replies Latest reply: Oct 17, 2016 11:28 AM by Sunny Talwar RSS

    Qliksense Pivot Table, Row(?) percentage instead of counts

    Samuel Lin

      Hello Qliker!!

       

      I have a pivot table that looks like:

       

      Capture.PNG

      The expression for counts right  now is very simple, it's just count(distinct EmailAddress)

      I want to replace, but unsuccessful so far, counts to % of the row. For example, for row #1 '0X~3X', each column should be about 10%, the first one under high is 10,307/97,373 and so forth.

       

      Any idea how I can achieve this?

       

      Thanks!

       

      Samuel

        • Re: Qliksense Pivot Table, Row(?) percentage instead of counts
          Samuel Lin

          just to be clear, I would like to get this:

           

          Capture2.PNG

           

          Thank you so much for the help!

          • Re: Qliksense Pivot Table, Row(?) percentage instead of counts
            Samuel Lin

            Hey Sunny,

             

            May I please get your help with a similar thing too? Again, I have a pivot table:

             

            PT1.PNG

            but the percentage is not calculating the way I want it.

             

            First of all, Digital Segment 3, is created as a dimension in the UI

            PT2.PNG

             

            when I want to apply your solution from last time, expression doesn't pick up Digital Segment 3 as a dimension

             

            given this, is there a way to calculate something like:

             

            PT3.PNG

             

            Thank you so much for your help!!

              • Re: Qliksense Pivot Table, Row(?) percentage instead of counts
                Sunny Talwar

                I think the best approach here would be to create the calculated dimension in the script and then you won't run into any issues here:

                Count(DISTINCT EmailAddress)/Count(DISTINCT TOTAL <Dimension2> EmailAddress)

                 

                But I doubt you will be able to do this with a calculated dimension.

                  • Re: Qliksense Pivot Table, Row(?) percentage instead of counts
                    Samuel Lin

                    You're right, Sunny.

                     

                    Here's the calculated dimension that I have:

                     

                    Aggr(

                      If([first order date]  > (today()-90), Dual('New', 1),

                    If([last order date] > (today()-270) and [TotalOrderCount] = 1, Dual('Recent OneTime Buyer', 2),

                      If([last order date] > (today()-270) and [TotalOrderCount] > 1, Dual('Active', 3),

                      If([first order date] <= (today()-90) and [last order date]  <= (today()-270) and [last order date] > (today()-365), Dual('At Risk', 4),

                      If([last order date]  <= (today()-365), Dual('Lapsed', 5),

                      If([FirstOrderDate] = '', Dual('Non Buyer', 6)

                      ))))))

                     

                    , EmailAddress)

                     

                    In the backend script load, I was trying to duplicate the dimension:

                    Load if([first order date]  > (today()-90),[EmailAddress]) as EmailAddress,

                      'New' as DigitalSegment_2

                    Resident [Full_List_Order_History];

                    Concatenate

                    Load if([first order date]  <= (today()-90) and [last order date] > (today()-270) and [TotalOrderCount] = 1 ,[EmailAddress]) as EmailAddress,

                      'Recent OneTime Buyer' as DigitalSegment_2

                    Resident [Full_List_Order_History];

                    Concatenate

                    Load if([first order date]  <= (today()-90) and [last order date] > (today()-270) and [TotalOrderCount] > 1 ,[EmailAddress]) as EmailAddress,

                      'Active' as DigitalSegment_2

                    Resident [Full_List_Order_History];

                    Concatenate

                    Load if([first order date] <= (today()-90) and [last order date]  <= (today()-270) and [last order date] > (today()-365),[EmailAddress]) as EmailAddress,

                      'At Risk' as DigitalSegment_2

                    Resident [Full_List_Order_History];

                    Concatenate

                    Load if([last order date]  <= (today()-365),[EmailAddress]) as EmailAddress,

                      'Lapsed' as DigitalSegment_2

                    Resident [Full_List_Order_History];

                    Load if([FirstOrderDate] = '', [EmailAddress]) as EmailAddress,

                      'Non Buyer' as DigitalSegment_2

                    Resident [Full_List_audience-1];

                    The problem with the backend load is that [TotalOrderCount] is from another table [Full_List_audience-1], and from reading some qlik discussion posts, I am still not clear how to have Resident from two tables...

                     

                    Appreciate  your help!

                      • Re: Qliksense Pivot Table, Row(?) percentage instead of counts
                        Sunny Talwar

                        There are few ways to go about this

                         

                        1) Join the two tables and do your manipulation on the joined table

                         

                        Table1:

                        LOAD....

                        Resident...;

                         

                        Left Join (Table1)

                        LOAD....

                        Resident....;

                         

                        Table2:

                        LOAD ....,

                                  NEW_FIELD....

                        Resident Table1;

                         

                        2) Use MappingTable/ApplyMap (preferable because you only need one field from another table)

                         

                        MappingTable:

                        Mapping

                        LOAD ....

                        Resident....;

                         

                        Table:

                        LOAD ....

                                   ApplyMap('MappingTable', FieldName) as NEWFIELD

                        Resident.....;

                         

                        3) Use Lookup function....