7 Replies Latest reply on Sep 13, 2018 1:57 PM by swaroop.gantela

# Cumulative values with random dates

Hi, I receive data which has cumulative values and random dates and so I want to standardise this and so they are comparable. That is, compare the current value to the previous value and then divide by the period between the two dates. I am new to Tableau but haven't been able to find any lessons or obvious help with how to do this. Can someone put me in the right direct or provide some direction.

Thanks

• ###### 1. Re: Cumulative values with random dates

Can you give an example how your data looks like? Just an Excel Sheet with Fake data.

• ###### 2. Re: Cumulative values with random dates

Thanks for quick reply. Attached a spreadsheet. Hope it is clear, added comment column and so you can see what my data means.

So, I want to have the appropriate annualised figure for each year value that exists and this requires reference back to the previous year recorded.

• ###### 3. Re: Cumulative values with random dates

Hi Deepak, do you need more details? It would be great to get a hand with this.

• ###### 4. Re: Cumulative values with random dates

Sorry, Still not clear from your description.

• ###### 5. Re: Cumulative values with random dates

Lisa,

I took a stab at it, but I'm not sure if I quite caught the gist.

To get the difference between two rows,  you can try:

IF FIRST()=0 THEN 0

ELSE SUM([Total Sold])-LOOKUP(SUM([Total Sold]),-1)

END

Similarly for the Time Difference and then divide the two.

I was unclear on account of the sample data not looking like it was cumulative for sales.

If you need to perform a running total on the Sales amounts first,

you can try a RUNNING_SUM(SUM(Total Sold)) calculation first

and then similarly calculate the difference:

IF FIRST()=0 THEN 0

ELSE [RunningSales]-LOOKUP([RunningSales],-1)

END

If these are not correct, please post a second sheet in the excel

1 of 1 people found this helpful
• ###### 6. Re: Cumulative values with random dates

Hi Swaroop,

Thank you very much. You have managed to work out basically what I wanted. I attach the spreadsheet with the results, one tiny tweak required whereby when Product and Type change you need a new FIRST. How would you do this?

Attached spreadsheet with actual results I want this time. Sorry if this was unclear. Thanks for taking the time to decipher it!

• ###### 7. Re: Cumulative values with random dates

Lisa,

To adjust the First value, two parts are needed:

1) the table calculation settings should all be set as shown below.

please note that there are often nested calculations which also need to be set.

you can click on the pulldown arrow marked in green below and cycle through each one.

2) you can try the following calculated field to specifically set the first value:

IF FIRST()=0 THEN [RunningSales]

ELSE [RunningSales Difference]/[Time Difference]

END