Hello, and thank you in advance for your help,
I've been driving myself nuts with trying to do this correctly. I am trying to build out a drill downs on cards according to the hierarchy of my company. These drill downs would show how often a user clicked on a link (%) at the lowest level, their organization above that, and then the company at the highest level. Eventually, I would like to show a summary drill down number that compares the lower level with the average against the entire company.
My question is two fold:
- However as you click down and filter... I can't find a way to reference the overall number above that. (listed again at the bottom in more detail)
- Is there a better way to do this, so I could show by date as well?
- Primary, so I could show improvement over time, however with needing to use the max value under each partitioning this dosen't seem possible. (More detail below)
- Secondarly, so that I can age out past campaigns. I believe the easiest way to do this is to filter prior to the partitioning action in ETL??
The data set is based on campaigns and users. For each campaign the user will have their own row, with a date of a click (or delivered) or a null value if no click was recorded. Their are multiple campaigns and users may or may not be in each campaign (in which case it would have a row with null fields after their name).
First I'm partitioning the data set on three levels:
Org (example Below)
SubOrg
User

Then I'm running beastmode calculations against those partitions to return the % at each level.
Examples:
(The 'Count' Columns are partitions)
Top Card:
PieValue = max(`Count of Clicked by Org`)/max(`Count of Delivered by Org`)
PieName = Org
Summary Number = count(`clickedAt`)/count(`deliveredAt`)
This seems to work at this point, and I get something that looks like this:

While the summary number isn't the average of the three Orgs, it should be accurate as "Org 3" is significantly larger than the other two combined.
Primary Question: If you were to click on 'Org 2' I would drill to the next level (below), and at that level I would like to be able to show "Your organization click rate is 8.49% compared to the Enterprise Benchmark of 7.59%. ( Specifically: I know how to do the concat statement, but not pull the unfiltered click rate (7.59%) in this case.).
