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

[Jono]

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?

[JeffW]

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 

[marcpaige]

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.

[KailaT]

Hi @Jono, Did Jeff's response answer your question?

[Jono]

Hi Kaila, I'm sorry I didn't respond earlier. I did try what he said, and it didn't work for me. However, the field I'm trying to use as a "measure" is a timestamp field. What I'm trying to do is count the number of times employees clock in on say Tuesday (today) and last Tuesday and then find the difference (through a formula) to show the change in count. I guess his advice didn't work for me because I'm counting using a timestamp field (it does seem to count by the way but doesn't work with the formula he gave me). If that's the problem, is there a way for me to convert the timestamp field into a new measure field and then apply his formula?