6 Replies Latest reply on Feb 19, 2016 5:25 AM by Noah Spitzer

# Calculate Last value minus First value by date, and a case statement

I'm trying to create a calculation that would vary by program. Any help would be appreciated as I've searched and beat my head against the wall all day. Thanks!

For Program A, I need a SUM(QTY)

For Program B, I need the Difference between the Last Value and the First Value

Example Result:

 PROGRAM QTY A 19 B 3

Example Data:

 MONTH PROGRAM QTY 1/1/2016 A 1 1/1/2016 B 2 2/1/2016 A 3 2/1/2016 B 7 3/1/2016 A 4 3/1/2016 B 2 4/1/2016 A 3 4/1/2016 B 6 5/1/2016 A 8 5/1/2016 B 5
• ###### 1. Re: Calculate Last value minus First value by date, and a case statement

Hey Noah!

You can do something like this maybe. I am hoping you have access to Tableau 9.0 though, because LOD expressions are only available post that version:

1st Qty: {fixed [Program]: sum(IF [Month] = {fixed [Program] : min([Month]) } then [QTY] end) }

Last Qty: {fixed [Program]: sum(IF [Month] = {fixed [Program] : max([Month]) } then [QTY] end) }

Calculation1:

IF attr([Program]) = 'A' then sum([QTY])

elseif attr([Program]) = 'B' then sum([Last Qty])-sum([1st Qty]) end

Hope this helps!

Pooja.

1 of 1 people found this helpful
• ###### 2. Re: Calculate Last value minus First value by date, and a case statement

Pooja,

Thank you so much for the assistance. We are using Tableau 9.2 and that gets me very close to the answer.

Only issue I am having with my data now is that because the 1st Qty and Last Qty are fixed by Program, if I change my date range the 1st and Last values don't change.

i.e. if I used only January and Febuary, then rusults would be as such:

 PROGRAM QTY A 4 B 5

Getting much closer though, and if I get it, I shall post my results.

Thanks!

• ###### 3. Re: Calculate Last value minus First value by date, and a case statement

Thats because filters are executed in a certain order in Tableau. Drag the date field to filters and click on the dropdown of the pill and select 'add to context'. The pill should turn gray:

Here is a great resource explaining the order of filter execution. Dimension filters are executed after the FIXED filters and hence ignores the dimensions placed in filter unless they are added to context because context filters are executed before FIXED.

1 of 1 people found this helpful
• ###### 4. Re: Calculate Last value minus First value by date, and a case statement

Thank you so much for your assistance. Providing the link has been most helpful as well!

• ###### 5. Re: Calculate Last value minus First value by date, and a case statement

Noah!

I am glad that helped. Yes, Tableau articles and videos are pretty helpful especially if you are starting out new. LOD expressions are very powerful in a lot of different use cases.

Overview: Level of Detail Expressions

Top 15 LOD Expressions | Tableau Software

FIXED Level of Detail Expressions

Here are a few more links that are useful if you want to try your hand at these LODs.

Have a great day!

Pooja.