
1. Re: Cumulative values with random dates
Deepak Rai Sep 9, 2018 9:42 PM (in response to Lisa Havas)Can you give an example how your data looks like? Just an Excel Sheet with Fake data.

2. Re: Cumulative values with random dates
Lisa Havas Sep 10, 2018 7:37 AM (in response to Deepak Rai)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
Lisa Havas Sep 11, 2018 4:41 PM (in response to Deepak Rai)Hi Deepak, do you need more details? It would be great to get a hand with this.

4. Re: Cumulative values with random dates
Deepak Rai Sep 12, 2018 11:22 AM (in response to Lisa Havas)Sorry, Still not clear from your description.

5. Re: Cumulative values with random dates
swaroop.gantela Sep 12, 2018 8:17 PM (in response to Lisa Havas)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
with your desired results.
Please see workbook v10.3 attached in the Forum Thread.

281399running.twbx 48.8 KB


6. Re: Cumulative values with random dates
Lisa Havas Sep 13, 2018 4:49 AM (in response to swaroop.gantela)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
swaroop.gantela Sep 13, 2018 1:57 PM (in response to Lisa Havas)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
Please see the workbook v 10.3 attached in the Forum Thread.

281399running2.twbx 43.6 KB
