1 of 1 people found this helpful
This problem requires several steps and without your workbook it would be difficult to explain
Last week I did something similar for some one who wanted to look at sales in quintile buckets - by State (US), category and year
I attached a T10.2 workbook for that solution - the general approach it to determine the boundary values between groups using the Percentile function
Then assigning records into groups based on those boundaries and then using nested LOD's expressions that support varying the groups and year while maintaining buckets at the state level
For presentation purposes I used a map of the US with years on pages to provide a dynamic view of how the sales quints changed with time
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
THANK YOU! I think this is what I need. I took a quick look at your workbook and realized that part of my problem was that I was trying to use RANK_PERCENTILE() instead of PERCENTILE() which resulted in an error about mixing aggregate and table calculations.
I will investigate further tonight and let you know how it works out.
Percentiles are one of the most misunderstood functions in Tableau (I might write a paper on that)
I used the model I sent you in a presentation last night -
after going through it I think they understood how to put it together
If you have any questions let me know
I have attached a version of my workbook. I could not figure out how to apply LOD to the calculation of the Tercile values. However, I believe that I was successful in calculating them correctly without LOD expressions.
Now I cannot figure out how to calculate the average of the scores within each tercile. Any help is greatly appreciated.
TestAnalysis.twbx 2.2 MB
see the attached
There are a lot of moving parts in your book and I am not versed in what you are actually goals -
I understand that you want to count the number of something in terciles but not clear what that is
- number of students by year/test grade /test window based on AVG score
- number of students by year/test grade /test window based on min score
- number of scores by year/test grade /test window
- a straight record count in each group
- or maybe something else
In your analysis you will want to determine the boundary point between the terciles based on that combination of dimension AND fix that number for comparing the actual measure to count how many fit in each group
That is where the LOD's come in -
there are 2 sheet (red tabes) in the workbook the first sheet looks like this
the first is the T1 calculation from your book - not an LOD
The second uses an lod to include the dimensions on your columns shelf
and the third nest the LOD form 2 to fix the result
In this view they all return the same values leaving the impression that you could use any of them to count the members in each group
But on the second tab I added Person ID - assuming that you wanted to put people into the groups and then do a count
both the first and second calculation above will vary by person - so are not a boundary that can be used to group people -
only the third - nested LOD is a fixed boundary that can be used for grouping
OK so what - the next step is to group something into the 3 pieces created by the boundaries but
I don't know enough about the goals her to first assure we are looking at the right level and second know what to place in each group and count
Does that make any sense?
TestAnalysis_v10.2 jd.twbx 2.3 MB