Tableau 8.2 - how to get max and min from % difference values on table?

Question

I'm facing problem in getting the max % and min % from a table containing % difference values.

Year-----A----------B---------C---------D---------Max %----Max Type----Min %----Min Type

2012

2013---4.30%---4.42%---4.34%---4.38%----4.42%---------B-----------4.30%---------A

The table above shows the % difference in sales from previous year. Thus 2012 shows no % (because there's no 2011). I used table calculation to compute the % difference, i.e. "Percent Difference From", compute using "Table (Down)" and "Previous".

The last four columns are what I'm having trouble doing. I want to get the max % and min % and also the corresponding types. I'm not trying to add the four columns to the existing table, but to get the correct results, as my ultimate goal is to display that results on the dashboard, i.e. on my dashboard, I want to display the highest % and its corresponding type; similarly the lowest % and its corresponding type. For example: on my dashboard, I want to display:

Highest % and type: 4.42% B

Lowest % and type: 4.30% A

So, I need to have the correct formulas to get the max % and min % and their types. These are what I did:

I tried to use WINDOW_MAX and WINDOW_MIN to display the max % and min % on the table but got funky wrong results.

1) I first get the formula in calculating the % difference from the "Customize" button from "Edit Table Calculation" window of SUM([Sales]): (ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1)) Then I created a calculated field of the above formula. I named the calculated field "Percent-Diff".

2) I created another calculated filed (named "Max % Difference") using the formula: WINDOW_MAX([Percent-Diff]). But it shows strange results. See image below. I don't know why it gives me 2.78% and 2.91% for 2012 and 2013 respectively. It should be 0% and 4.42% for 2012 and 2013 respectively. Something is not correct.

If it is just SUM([Sales]) instead of % difference, then I get the correct result of showing the max sales using the formula WINDOW_MAX(SUM([Sales])).

3) Also I don't know how to get the corresponding type. I tried using the formula: IF [Max % Difference] = [Percent-Diff] THEN ATTR([Product Type]). But it returns:

NULL

B

I'm not sure if the formula is correct. It looks correct on the result (i.e. "B" is correct), except that it also shows a NULL value which I don't know why. I think it's because I didn't include the ELSE part in my IF formula? But why the NULL value is shown as the first value? I want the formula to return just one value, "B". So, how to only just show "B"?

I've posted twice the problem in tableau forum, but as of now, nobody has answered my problem. I believe that my formulas are incorrect. So, if anyone here can correct the formulas to get the max % and min % from % difference values and also to get the corresponding type, then it'd be very much appreciated. Thanks a million!

Answer 1

• It's hard to tell not knowing how your database looks like (as you didn't explicitly presented it, but I can try to infer based on the clues you left on your post). But I could reproduce something like you said using the Sample - Coffee Chain Database, and it worked out well, calculated yoy sales increase by product and then window_max of that.

• What you're probably missing is the partitioning. I suggest avoiding using Table or Pane to create the partitions in more complex situations (as it will work only in that specific arrangement of fields), but rather use the dimensions to partition it.

• So, your [Percent-Diff] field should be computed using [Date], and your [Max % Difference] should be computed using [Product Type]. IMPORTANT, for [Max % Difference], when you go to Edit Table Calculation, you'll have to choose the Compute using for [Percent-Diff] as well (you can choose on the top of the window)

• Your formula to find which type is the max (or min) is also correct (and should only respect the partitions). Nevertheless, it is very hard to have the exact output you're expecting.

• What I would do is to create 2 spreadsheets (and later combine them in a dashboard).

• The first would be what you already got (Each product [Percent-Diff]

• The second one I would change your formula (3) to just [Max % Difference] = [Percent-Diff], and use it as a filter (filtering only true). I would drag both Date and Product to the sheet (you choose if you want it on columns, rows, or just detail) so I can use them to partition the table. And drag [Max % Difference] to be visualized. That way you'll only see the product that is the max, and how much is that max.