cancel
Showing results for
Did you mean:

## Formula to calculate differences from one week count to past week count

Cosmonaut

If I have an Incorta dashboard as such:

date                      count       difference formula

10/10/2022              10              0 (N/A)

10/17/2022              28             18

And I want to see the difference between the count value for date 10/17 and the count value for date 10/10 (week earlier), manually writing this the math is 28 - 10 = 18 (difference)

How would I construct an Incorta Insight formula to calculate that difference in either a listing, aggregate or perhaps pivot table?

6 REPLIES 6
Employee

Jono,

Are you really summing the "week" or are you picking specific days (value on 17th and 10th),  If you look at the syntax for the sum() function in Inclorta, you will see that you have 2 options:

sum(Sales.Sales.AMOUNT_SOLD)

or
sum(Sales.Sales.UNITS, groupBy(Sales.Product.PRODUCT_CATEGORY))

Therefore, you could sum grouping by the date, and then subtract the sum grouped by the date - 7.  and for that part we have a addDays() function.

try something like (sum(amount, groupBy(date)) - sum(amount, groupBy(addDays(date,-7))))

Hope that this helps.
Jeff
Astronaut

Jeff, a comment on the try suggestion: when I try to do this I get this error:

INC_04050708: The group by columns in the level based measure should be used in the grouping dimensions and have the same order Date_Time.Date.Date.

We have found a way to do this using an MV.

Employee Alumni