Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.