Create bucketing for treemap chart?

[shempgracie]

Hi All,

I am using a TreeMap chart to show the current headcount by department for an organization.  The Treemap chart allows you to add a 2nd dimension to do grouping.  I would like to group this data into two categories:

1.  Organizations where Actual headcount > Budget headcount (Over Staffed)

2. Organizations where Actual headcount < Budget headcount (Under Staffed)

I tried this statement by adding it in the expression for the 2nd dimension, but the visualization won't display.

=If (SUM(Budget) - SUM(Actual) > 0, 'Under Staffed','Over Staffed')

Any thoughts on what I am doing wrong?

Thanks!

Mike

[treysmithdev · Accepted solution]

You need to add an AGGR for this to work in a dimension.

Try this:

=If(Aggr(SUM(Budget) - SUM(Actual),Organization) > 0, 'Under Staffed','Over Staffed')

[stevedark]

Hi Mike,

It depends a bit on your data model, do you have an Department table with Budget and Actual on it?

If so, create a new field in the load script:

LOAD
   Department,
   Actual,
   Budget,
   if(Actual > Budget, 'Over Staffed', if(Actual < Budget, 'Under Staffed', 'Perfect!')) as [Staffing Level],
   etc...

If there are multiple rows then you will need to group values to have a field with the value in. These work in pretty much the same way as in SQL. You would have something like:

Staffing:
LOAD
   Department,
    if(sum(Actual) > sum(Budget), 'Over Staffed', if(sum(Actual) < sum(Budget), 'Under Staffed', 'Perfect!')) as [Staffing Level]
RESIDENT [Existing Table]
GROUP BY Department
;

This requires a unique department name. If the same departments exist in different organisations you will need to create a composite key.

Apart from performance, the other big advantage of working things out in the load script is it will not be messed up by selections.

Hope that helps.

Steve

[shempgracie]

Hi Treysmithdev,

Your solution worked!  Thank you very much!

There is just one problem I have with it though.  So the dimension I have associated with my Treemap chart is a drilldown dimension of cost centers (Department --> SubDepartment --> Cost Center).  

Our data is setup like this so it is very easy to drilldown through the departments.

Department, Subdepartment, Cost Center, Actual, Budget

My treemap is now grouped properly, but in order to get it to do that I had to specify my cost center field, but now the drilldown doesn't work anymore.  I tried to make it the name of my drilldown dimension (DeptDrilldown) but that didn't work.

=If(Aggr(SUM(Budget) - SUM(Actual),MyCC) > 0, 'Under Staffed','Over Staffed')

Any thoughts on how to keep the drilldown working, and use your formula below for grouping?

Thank you!

Mike

[shempgracie]

Hi Steve,

Thank you for this solution! I may need to go this route if my challenges with the AGGR function don't resolve.  Right now the AGGR function groups my data in the treemap, but it broke my drilldown ability.  I may end up doing your suggestion and tag the data on the ingestion to indicate under/over, and then use that dimension to group by.

Thank you!

Mike

[stevedark · Accepted solution]

I find that the more that you can do in the load script the better, in general.

To fix your Aggr it may just be a case of adding all the dimensions used in the TreeMap as parameters of the Aggr. You can have as many parameters as you need.

=If(Aggr(SUM(Budget) - SUM(Actual),Department,SubDepartment,MyCC) > 0, 'Under Staffed','Over Staffed')

If that doesn't work you may need to work out what level your drill down is at in code;

=If(Aggr(SUM(Budget) - SUM(Actual),$(=if(GetPossibleCount(Department)>1, 'Department', if(GetPossibleCount(SubDepartment)>1, 'SubDepartment', 'MyCC'))) ) > 0, 'Under Staffed','Over Staffed')

Good luck.

[shempgracie]

Hi Steve,

Adding the parameters to the AGGR worked perfect! Now my Treemap behaves perfectly.  Thank you so much!

Ultimately I am going to follow your advice and fix the data on ingestion, but just to get me through next week's demo this is perfect.

Thanks again!

Mike