Help with Pivot Table /Set Expression

[#1]

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.

[petter · Accepted solution]

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.

[#3]

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.