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?

[RADSr]

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-i...

[Jono · Accepted solution]

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, 

    0

) - lookup(

    schema_A.table_A.count_field, 

    schema_A.table_A.date_field, 

    addDays(

        schema_B.table_B.date_field, 

        -7

    ), 

    0

)

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.