
1. Re: how to compare aggregate value with corresponding cutoff
Jennifer VonHagel Nov 2, 2018 9:15 PM (in response to Mei Ming)Hi Mei Ming,
This is certainly doable! Can you attach a .twbx workbook with sample data? The answer will depend on how your data is set up, particularly the relationship between actual values and category minimums.
Best,
Jennifer

2. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Nov 4, 2018 6:26 PM (in response to Jennifer VonHagel)Hi Jennifer,
Thank you for your response. I am not sure what is the best way to display the relationship between aggregated values with minimum criteria. Right now I have the original raw data which I used for aggregation for department/category and another excel sheet for category minimum. I joined those two sheets by category name, so the view output would have the minimum as the dimension and aggregated values as measure. I will figure out how to upload the sample data if that is helpful.
Do you have any better suggestion for better displaying the data so that I can get what I want.
Thanks a lot,
Mei

3. Re: how to compare aggregate value with corresponding cutoff
Jennifer VonHagel Nov 4, 2018 6:50 PM (in response to Mei Ming)Hi Mei,
Yes, if you could attach a .twbx with the data as you have it that would be great. It will help me (and the community) make more specific suggestions or recommendations both for your original request and if there might be a better way to connect the data. .
Best,
Jennifer

4. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Nov 4, 2018 8:10 PM (in response to Jennifer VonHagel)Hi Jennifer,
I attached sample.twbx file for your reference. You will see I have two different data sets  one is the raw (one row is one transaction) and another is the minimum requirement data. I joined them with department and category, then get an aggregated value for each department/category (see sheet1) with minimum requirement for each dep/category. Basically I would like to get a heatmap to mark those sum(number of record) below minimum for each dep/category, and also compare the department performance with some metrics (such as unique saleId percentage below minimum  (number of saleId with at least one category below minimum/total count of saleId for each dept), category percent below cutoff  total categories (unique count and nonunique count) below minimum for all saleID/total categories (count of saleId * count of category for each dept).
Let me know if you need any additional info and thanks again for your help.
Thanks,
Mei

sample.twbx 10.1 KB


6. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Nov 5, 2018 10:03 AM (in response to Jennifer VonHagel)Yes, Jennifer. I want to compare the minimum with count for each saleId as you marked in above snapshot. Typically I also want to see percentage of saleId (unique count saleId) with at least one category below cutoff for each dept.
Thank you so much for your help!
Thanks,
Mei

7. Re: how to compare aggregate value with corresponding cutoff
Jennifer VonHagel Nov 5, 2018 12:34 PM (in response to Mei Ming)Hi Mei,
I've attached the workbook with some calculations and sample charts that should get you started. Have a look and let me know if you have questions. If you haven't worked with LOD (Level of Detail) calculations before (FIXED, INCLUDE, EXCLUDE), see here: Level of Detail Expressions  Tableau . In the calculations below, I've only used the FIXED type of LOD.
Here are a few calculations I created as a basis:
Item Cnt: This is the same as [Number of Records], but more descriptive for calculations.
1
Trx Row: This simply creates an explicit dimension which is the level at which to compare Number of Items to your Minimum (or Target)
[Sale Id] + '_' + [Department] + '_' + [Category]
Trx Row Variance: Finds the difference between the Item Cnt and Minimum for each Trx Row. I've wrapped it in "ATTR" because we don't want to SUM() across rows, we simply want the number at the Trx Row level of detail.
ATTR(
{ FIXED [Trx Row] : SUM([Item Cnt]) }  [Minimum]
)
Trx Row Variance %: Finds the variance as a % from the Minimum or Target.
ATTR(
({ FIXED [Trx Row] : SUM([Item Cnt]) } / [Minimum])  1
)
Trx Row Meets Target: Categorizes each TrxRow as True or False based on whether its Item Cnt is below the minimum or not.
{ FIXED [Trx Row] : SUM([Item Cnt]) } >= [Minimum]
Target: This is a copy of [Minimum], but classified as a measure rather than a dimension. For some types of charts it's better for this to be a measure.
With these calculations, you can do standard Actual vs Target, Variance type charts:
Not sure what you had in mind for a heatmap, but here's one option. Bar charts are easier for me to visually process.
And here are some calculations that will help you find number of SalesIDs per Department that have at least one TrxRow not meeting the Minimum:
SalesID Uq Cnt:
COUNTD([Sale Id])
SaleIDs has TrxRow < Minimum: TrueFalse field for each Sale ID whether that Sale ID has at least one Trx Row not meeting the minimum
{ FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }
% SaleID TrxRow < Min: % Sales IDs which have at least one Trx Row not meeting the minimum. In your sample data, this is all of them.
COUNT({ FIXED [Sale Id] : MAX(IF NOT [Trx Row Meets Target] THEN 1 END) }) /
COUNTD([Sale Id])

sample.twbx 86.8 KB


8. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Nov 5, 2018 2:10 PM (in response to Jennifer VonHagel)Hi Jennifer,
This looks amazing. However, I won't be able to open the attached workbook you uploaded. I guess it may be caused by the different version of tableau. I am currently using 10.3. Is it possible for you to reattach it?
Thanks,
Mei

9. Re: how to compare aggregate value with corresponding cutoff
Jennifer VonHagel Nov 5, 2018 2:37 PM (in response to Mei Ming)Oh right, see if this works, I had to switch out datasources (from .hyper to .tdsx)  hopefully everything translates ok.
Best,
Jennifer

sample_v10.3.twbx 93.2 KB


10. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Nov 6, 2018 11:49 AM (in response to Jennifer VonHagel)Thanks, Jennifer. It works pretty well. I will implement a little bit and change the mark here as 'answered'.
I am deeply grateful for your help!
Thanks,
Mei

11. Re: how to compare aggregate value with corresponding cutoff
Mei Ming Dec 4, 2018 1:10 PM (in response to Jennifer VonHagel)Hi Jennifer,
Thank you for your help with this issue.
In above case, I merged original dataset with minimum requirement into one with join. However, I came across another case, in which I have to do a lot of aggregation to merge/combine some categories into new categories, and also have the cutoff for new categories, but won't be able to merge them at the beginning as in the previous case. I can use data blending, but level of details can only be used in one data source.
Would you have any idea how to compare them in terms of heatmap?
Thanks,
Mei

12. Re: how to compare aggregate value with corresponding cutoff
Jennifer VonHagel Dec 4, 2018 4:22 PM (in response to Mei Ming)Hi Mei,
Without seeing the complexities of your data transformations (how things are aggregated, merged, combined; the difference in levels of detail between the blended data sources), I don't know what to recommend. I would need to see the data and tinker with it for a solution. Can you attach a .twbx workbook that is representative of the complexities of your real data?
Best,
Jennifer