9 Replies Latest reply on Mar 8, 2016 8:49 AM by Sash L.

# Getting the previous value within State and Category

Hi,

Attached some sample data and a simple table. I want the table's tooltip to show the previous period's value of the same category, and if possible, the previous period (so in the tooltip should read e.g.: "Previous period (xxxx)'s value: 5.2").

My issue is to get a calculated field that gives me this info. I could not manage to combine a FIXED with Previous_value (I assume this is what I have to do)? Still a bit lost with these basic tableau functions it seems.

Any help is appreciated!

Edit: better sample data

• ###### 1. Re: Getting the previous value within State and Category

Does the date need to change? If not, then you can do something like this, which will show last year's value:

{FIXED [State], [Category] :

SUM(IF YEAR({MAX([Date])})-1 = YEAR([Date])

THEN [Value]

END)

}

1 of 1 people found this helpful
• ###### 2. Re: Getting the previous value within State and Category

Dear Derrick,

many thanks, this is indeed the case so I can use your suggestion. One question: how would I do this with quarterly data?

• ###### 3. Re: Getting the previous value within State and Category

Hey Sash,

To do this with quarterly data, you can do one of two things.

A) If you can convert the quarters to a numeric index, you can do simple math.

B) If not, you can make a longer statement inside the SUM instead of the YEAR() logic. IE:

IF DATEPART('quarter', [Date]) = 3 AND DATEPART('quarter', {MAX([Date])} = 4 AND YEAR({MAX([Date])}) = YEAR([Date]) THEN [Value]

ELSEIF ...

1 of 1 people found this helpful
• ###### 4. Re: Getting the previous value within State and Category

Dear Derrick,

I don't think I can follow you. Why quarters 3 and 4 in particular? My dates are continuous. Apologies, I just do not quite understand.

• ###### 5. Re: Getting the previous value within State and Category

Sorry, I meant you'd need to create a case for each quarter - matching the current quarter to what the "previous quarter" would be:

Something like this:

IF DATEPART('quarter', [Date]) = 3 AND DATEPART('quarter', {MAX([Date])}) = 4 AND YEAR({MAX([Date])}) = YEAR([Date])

THEN [Value]

ELSEIF DATEPART('quarter', [Date]) = 2 AND DATEPART('quarter', {MAX([Date])}) = 3 AND YEAR({MAX([Date])}) = YEAR([Date])

THEN [Value]

ELSEIF DATEPART('quarter', [Date]) = 1 AND DATEPART('quarter', {MAX([Date])}) = 1 AND YEAR({MAX([Date])}) = YEAR([Date])

THEN [Value]

ELSEIF DATEPART('quarter', [Date]) = 4 AND DATEPART('quarter', {MAX([Date])}) = 1 AND YEAR({MAX([Date])}) = YEAR([Date])+1

THEN [Value]

END

• ###### 6. Re: Getting the previous value within State and Category

Dear Derrick,

this is my bad, I was tired and didn't specify the exact date format of my data.... I attached a new workbook that has the features I need:

- Has the date in the format I have, i.e. dd/mm/yyyy (for quarterly and monthly)

- has quarterly and monthly series (State: Ohio/Category: Apples is monthly, the rest is quarterly)

I could not get your code to work (error: "cannot mix aggegate and non-aggregate comparisons or results in IF expression").

Many thanks..

• ###### 7. Re: Getting the previous value within State and Category

I tried using a similar statement as the one I use to obtain the maximum date, i.e. {FIXED [State],[Category]: MAX(IIF([Value] <> 0, [Date],NULL))} and incorporate a -1 term, but this does not work. Any suggestions?

• ###### 8. Re: Getting the previous value within State and Category

Here you go Sash,

I went about it a slightly different way seeing the data.

Hope this helps - let me know if you have any questions about it!

1 of 1 people found this helpful
• ###### 9. Re: Getting the previous value within State and Category

This worked for me, and will help with similar exercises inthe future, so thanks!