3 Replies Latest reply on Jul 21, 2016 1:08 PM by Derrick Austin

# How to get Date for corresponding to '40%' of maximum value?

Hi,

I am new to Tableau.

I have a dataset in the following format:

 Date Pub Impression 12/1/13 Abc 10 10/1/14 Abc 20 11/1/14 Abc 50 12/1/14 Abc 35 1/1/15 Abc 12 12/1/13 xyz 20 10/1/14 xyz 40 11/1/14 xyz 80 12/1/14 xyz 12 1/1/15 xyz 3 12/1/13 pqr 35 10/1/14 pqr 55 11/1/14 pqr 70 12/1/14 pqr 25 1/1/15 pqr 10

If you notice that the impressions for each 'Pub' reach a max and then go down.

I know how to calculate the max impression for each Pub. I want to know the date when impressions hit 40% of the max impressions mark on their way down. Note that the above data is just for example. The actual data is far more complex but follows a similar pattern.

Please comment if you need further info to answer the question. Any help is appreciated.

Thanks.

• ###### 1. Re: How to get Date for corresponding to '40%' of maximum value?

Hey Chin,

What if you did some nested LoD calculations?

To get the MAX in a comparable format: {FIXED [Pub] : MAX([Impression])}

Then, you can compare that to the current:

{FIXED [Pub], [Date] : IF MAX({FIXED [Pub] : MAX([Impression])} * .4) < SUM([Impression]) THEN [Date] END}

If you need it only on the way down, you might go a step further and compare the MAX impressions to the impressions for the date to get the date of the MAX.

• ###### 2. Re: How to get Date for corresponding to '40%' of maximum value?

I stared at this for a while before I got the logic. Awesome. I have already used the Fixed calculation for MAX([Impression]).

Getting back to the point, I got the following error:

Cannot mix aggregate and non-aggregate comparisons or results in IF expressions

• ###### 3. Re: How to get Date for corresponding to '40%' of maximum value?

Oh, you probably need a MAX() on the date in the IF statement (If that doesn't help, maybe a screenshot of the calculated field window?)

{FIXED [Pub], [Date] : IF MAX({FIXED [Pub] : MAX([Impression])} * .4) < SUM([Impression]) THEN MAX([Date]) END}