2 Replies Latest reply on Apr 24, 2017 3:38 AM by Vijaya Kumar Reddy Chinnam

# Calculating Difference between most recent and least recent values

Below is the content from csv file , I use. For each Code , we want to calculate difference of "rate" for each "code" on most recent and least recent rates and we want to sort "Codes" on rate diff.  Please find attached workbook. I created fixed LOD expressions and diff calculation.  But diff calculation is 0 for all three codes.

But it should be  30 (i.e. 40-10) for "code10",  31 (i.e. 39-8) for "code11" and 28 (i.e. 39-11) for code12. Can anybody tell, how can it be achieved in Tableau.

code,group,item,effective date,rate

code10,group1,item1,2017-04-01,10

code10,group1,item1,2017-04-02,5

code10,group1,item1,2017-04-03,50

code10,group1,item1,2017-04-04,40

code11,group1,item1,2017-04-05,8

code11,group1,item1,2017-04-06,5

code11,group1,item1,2017-04-07,50

code11,group1,item1,2017-04-08,39

code12,group1,item1,2017-04-05,11

code12,group1,item1,2017-04-06,3

code12,group1,item1,2017-04-07,55

code12,group1,item1,2017-04-08,39

• ###### 1. Re: Calculating Difference between most recent and least recent values

Hi Vijaya,

I'm using v10.2 so can't attach a workbook, but I slightly changed the formulas for Most and Least Recent Rate. If you use:

Most Recent Rate

{FIXED [Code] : sum(if [Most Recent Date]=[Effective Date] then [Rate] END)}

and

Least Recent Rate

{FIXED [Code] : sum( if [Effective Date]=[Least Recent Date] then [Rate] END)}

That should get you what you're looking for.

Let me know if that helps

Michael

EDIT: I realized an error in the formulas. Should be fixed now!

2 of 2 people found this helpful
• ###### 2. Re: Calculating Difference between most recent and least recent values

Thanks Michael !!! This solution works for me.