
1. Re: Help calculating dynamic quartiles
Bryce Larsen Jun 7, 2018 10:17 AM (in response to Keir Blockley)Could you save this in 10.2 or 10.3? I'm unfortunately not on the newer version of tableau yet. Or you could post the data as well.
Having said that, you could look to utilize the PERCENTILE() function.
Example from Superstore. I created this Calculated Field and converted to Dimension:
IF [Sales] <= {fixed: PERCENTILE([Sales], .25)} THEN 1
ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .5)} THEN 2
ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], .75)} THEN 3
ELSEIF [Sales] <= {fixed: PERCENTILE([Sales], 1)} THEN 4
END
Then I simply put on Number of Records and changed to Running Total:
Adding a secondary table calculation for reassurance:
Hope this helps!
Bryce

2. Re: Help calculating dynamic quartiles
Keir Blockley Jun 8, 2018 6:27 AM (in response to Bryce Larsen)Bryce
Yes it has helped thank you.
Is there a feature in tableau to make the quartiles have an even amount of records? I can understand why it has apportioned the records as it has though, as there are multiple records with the same hourly rate, so it doesn't "neatly" fit into 4 quarters.
Thanks
Keir

3. Re: Help calculating dynamic quartiles
Bryce Larsen Jun 8, 2018 9:11 AM (in response to Keir Blockley)Hmm. Sorry, I'm not really sure I follow. What do you mean an "even" amount of records? Can you provide an example?
And glad it at least got you started!

4. Re: Help calculating dynamic quartiles
Keir Blockley Jun 11, 2018 4:47 AM (in response to Bryce Larsen)Bryce
I meant that so that each quartile has an equal amount of records within each one e.g. if there are 400 records in total, there would be 100 in each quartile.
The issue is that, as my data has lots of records with the same hourly rates, it doesn't split the quartiles evenly.
Keir

5. Re: Help calculating dynamic quartiles
Bryce Larsen Jun 11, 2018 6:33 AM (in response to Keir Blockley)Ah, I see. This is difficult and not sure I would really refer to these as 'quartiles' then, at least not in the statistical sense.
Unfortunately there's no level of detail expression for Rank. You can do it on the view if showing all records, but you can't store it in the data. I might suggest doing this prior to bringing it in. If you can sort your data by Hourly Rates and assign IDs in this way (by Gender if desired), then you could do it in that way.
{fixed [Gender]: MAX([ID])} returns Max ID in the data per each gender. And then you can do something like:
{fixed [Gender]: MAX(
IF [ID] < [Max ID per Gender]*.25 THEN 1
ELSEIF [ID] < [Max ID per Gender]*.5 THEN 2
ELSEIF [ID] < [Max ID per Gender]*.75 THEN 3
ELSE 4
END)}
That might work and return the group per each record by gender.
Bryce

6. Re: Help calculating dynamic quartiles
Keir Blockley Jun 11, 2018 8:51 AM (in response to Bryce Larsen)Bryce
I don't think I can do the above, as the dashboard will have multiple filters, which will change which quartile a record will be in potentially. I think I am expecting too much for it to do the quartiles perfectly.
Thanks for your help with this, I am using the solution in your original response.
Keir