So you’re comfortable with general LODs at a particular level of granularity, but how do you feel about working at different levels within the same calculation?
I’ve always found nested LODs confusing, so I tend to build calculations up in tables. In this example I’ll be answering the question:
Within each product category, what is the most common reason for complaint?
To count the number of cases, I will be using a COUNTD() of my Case IDs.
The question addresses two levels of detail:
- The number of complaints citing each reason
- The maximum (number of complaints for each reason) within each product category.
Level 1:
We need to find the number of complaints, for each reason
We need to add in the product group to the LOD because otherwise, this would give us the number of complaints for each reason across all product categories.
The result of this calculation should be the exact same as what is in your view, as a sense-check:
Level 2:
In the same calculation, now we need to find the maximum of this value within each product group:
Taking our first LOD, wrap it in a MAX(), fixed on Product Group.
Level 3:
What if we want to bring back the reason associated with that max value? You guessed it, another LOD:
For each product group we want to perform a test, i.e.
- If the [# of complaints for each reason within each product group] is equal to the [max [# of complaints for each reason within each product group]] bring me back the case reason - this needs to be wrapped in a MIN/MAX as LODs must be aggregated in some way.
- Do this FOR EACH product group - this will be your outermost LOD
And when you put this on your rows, you’ll be able to identify the most common reason:
One final thing to note is that LODs don't have to be nested, if you want to create separate calculations which reference each other, this is equally valid and may be more transferable for e.g. client handovers.
