2 Replies Latest reply: Jun 15, 2015 11:38 PM by Behezad Mehta RSS

    Help with Pivot Table /Set Expression

      I need help with a specific problem - example data below.

       

      Not sure if this is possible using Pivot Tables and Set expressions or if more complex code will be needed.

       

      NOTE : I am using the QlikSense free desktop version and not QlikView

       

      Headers : Project Name Account Name Project Manager Project $

       

      Example

       

      Project Name   Account Name    Project Manager     Project $  Project Status

      P1                     A1                       PM1                        10             New

      P2                     A1                       PM1                        10             New

      P3                     A2                       PM2                        20             New

      P4                     A1                       PM1                        30             Old

      P5                     A3                       PM2                        05             Old

       

       

      My end aim is to get a table and chart by PM listing the

      total # of projects, total # unique accounts, # projects >10$, # accounts with total project $ > 10

      AND the project status will be the filter

       

      Using a pivot we can get the # projects, # unique accounts, # projects $>10 and I have added a filter pane with status.

       

      The challenge is coming up with # Accounts >$ because the original table has project $ by project name and not account.

       

      So I can create a separate pivot by Account and determine if the account total project $ > 10 but how do I feed that into the first pivot to get a count ?

       

      Thanks in advance for your help.

        • Re: Help with Pivot Table /Set Expression
          Petter Skjolden

          I think this expression should do it:

           

          Count( Aggr( If( Sum( [Project $]) > 10 , 1) , [Account Name]) )

           

          First give a tally of the sum of projects for every account that exceeds 10 - then count them.

           

          To be able to have nested aggregations you will have to resort to Aggr() - unless you have a TOTAL qualifier.

            • Re: Help with Pivot Table /Set Expression

              Thanks!!! That Worked.

               

              But shouldn't the expression be Count( If(Aggr( Sum( [Project $]), [Account Name] )>10,1))

               

              I'm not sure how your expression with the IF nested inside the Aggr is giving the same result as the expression above ?

               

              Is there a way to plot a chart directly from the Pivot table ? Right now I have to recreate the measures ?

               

              Is there an efficient way to do the pivot and aggregations in excel ? Not everyone in my team uses QlikView.

               

              Thanks again.