3 Replies Latest reply on Jun 11, 2018 8:03 AM by Zhouyi Zhang

# Calculate Delta % from previous time period per customer

Hi all,

As i am trying to create a view which show the difference % from previous time period for customers i got kinda stuck.

Table calculation works is only relatable to the previous data in a chart and does not refer back to the customer's specific data from last time period.

It might work by using a calculation of this month and a calculation from last month where you specify the customer in the calculated field.

As my knowledge is not going that far I am asking you for help.

To illustrate you can use my template which is attached to this discussion.

The ideal outcome would be:

Customer on X axis

Delta % as value without having to show current and last months data.

Thank you,

Miquell

• ###### 1. Re: Calculate Delta % from previous time period per customer

Hi Miquell,

Try this calculation:

(sum(IF DATETRUNC('month',[NEW ORDER DATE]) = DATETRUNC('month', TODAY())

then [Sales] END)-sum(if

DATEPART('month',[NEW ORDER DATE]) = DATEPART('month', TODAY())

and year( DATEADD('year',1, ([NEW ORDER DATE]))) = YEAR( TODAY())

then [Sales]

END) )/

sum(if

DATEPART('month',[NEW ORDER DATE]) = DATEPART('month', TODAY())

and year( DATEADD('year',1, ([NEW ORDER DATE]))) = YEAR( TODAY())

then [Sales]

END)

This is comparing the month of today (June) with previous year's June value.

I've attached the workbook for you to have a look.Thanks,

Mavis

• ###### 2. Re: Calculate Delta % from previous time period per customer

Miquell,

You can accomplish this with a few calculations.

- a LOD calc to find the latest month in the data, to serve as your Current Month

- another calculation to determine the Previous Month based on the above

- a set of CM/PM calculations for the metric you're interested in.

This will calculate the current and previous month's values without the need for displaying the date dimension.  Then you can calculate the period over period change with a straight calculation rather than a table calc.

Workbook is attached so you can see my calculations.  Hope this helps.

- Jim

• ###### 3. Re: Calculate Delta % from previous time period per customer

Hi, Miquell

I assume your real data will have more month rather than 2 in your sample workbook.

If that is the case, please find my solution attached as well as steps below

1st. union your data to itself

2nd, create two calculation fileds

and result

Hope this helps

ZZ