topic Re: Formula to calculate differences from one week count to past week count in Data & Schema Discussions

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

Jono — Fri, 21 Oct 2022 21:06:19 GMT

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?

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

JeffW — Mon, 24 Oct 2022 14:52:21 GMT

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)

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

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

KailaT — Tue, 01 Nov 2022 15:09:13 GMT

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

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

Jono — Tue, 01 Nov 2022 15:53:17 GMT

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?

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

RADSr — Tue, 01 Nov 2022 16:51:02 GMT

Use a formula column at the physical schema to change the clockin date/time into an integer w/ the value of 1 will let you do a sum instead of a count.

You'll still have the issue, just taking a step to make things easier.

Reference this thread for a similar issue: https://community.incorta.com/t5/dashboards-analytics-discussions/what-is-a-simple-excel-reference-is-hard-in-incorta/td-p/3051

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

Jono — Fri, 04 Nov 2022 15:08:59 GMT

Thank you to all who gave me hints at a solution. However, kudos to my manager who actually solved the issue for me. The solution involved using Lookup similar to this example below.

lookup(

schema_A.table_A.count_field,

schema_A.table_A.date_field,

schema_B.table_B.date_field,

) - lookup(

schema_A.table_A.count_field,

schema_A.table_A.date_field,

addDays(

schema_B.table_B.date_field,

-7

)

NOTE: for our situation a materialized view (schema_A) was necessary to create a count field because lookup will not work with other functions and so the count function had to be used ahead of time to create a new field with the count already in it; then lookup could work with that field that had the count; also he set up the date field in the materialized view (schema_A) as a key field since lookup function requires a key.

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

marcpaige — Fri, 04 Nov 2022 19:41:50 GMT

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.