Can you give an example how your data looks like? Just an Excel Sheet with Fake data.
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.
Hi Deepak, do you need more details? It would be great to get a hand with this.
Sorry, Still not clear from your description.
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)
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
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
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!
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]
Please see the workbook v 10.3 attached in the Forum Thread.
281399running2.twbx 43.6 KB