Calculate Sum Of a Field Based on Custom Date Range and Offset

Q&A
,
1

I have a dashboard with a date filter that shows certain KPIs. Roughly, my data is something like
Date | NumberOfCalls|
2024-07-01 | 5 |
2024-07-02 | 4 |
2024-07-03 | 3 |
2024-07-04 | 2 |

So I have a basic display like this that sums number of calls over the date range and displays the total

image
image822×707 11.3 KB

I would really like to show a percent change in the bottom of the KPI that shows how the data compares to the same number of days preceding the custom date selection. E.g., if the user selects 2 days [2024-07-03 - 2024-07-04], I can show change from the number of calls that occurred [2024-07-01 - 2024-07-02].

I’ve tried using dateDiff(${StartDate}, ${StopDate}) to get an integer I could apply as an offset to a function like periodOverPeriodDifference, but that is not producing a valid value for the function. Is there another approach I could use?

2

Hi @ryan_illuma,
I believe that this may be hard to accomplish as the number of days will vary based on your date selection(s).
Though a work around may be to create a second KPI next to this one to compare. You could try something like

addDateTime(-(dateDiff(${StartDate}, ${StopDate}, 'DD'), ${StartDate}, 'DD')

This can replace your filter:

ifelse({date} >= **above formula AND {date} < ${StartDate}, {value}, NULL)

Let me know if you have any additional questions!

3

Hi @Brett ,

Thanks for the suggestion. I’m having an issue with the formula though, I think because dateDiff expects two date fields, and not parameters representing single dates, as is the case with StartDate and StopDate.

I’m getting the following error:
Expression addDateTime(
-dateDiff(${StartDate}, ${StopDate}, ‘DD’),
${StartDate},
‘DD’
) for function addDateTime has incorrect argument type addDateTime(Number, Date, String). Function syntax expects Numeric, String, Date.

I think you can get the same behavior with hardcoded dates like the following

addDateTime(
    -dateDiff(parseDate('2024-07-05'), parseDate('2024-07-03'), 'DD'), 
    parseDate('2024-07-05'), 
    'DD'
)
4

Hi @ryan_illuma,
Apologies for the delayed response. It looks like the order may just be incorrect.

addDateTime(-dateDiff(${StartDate}, ${StopDate}, ‘DD’), 'DD', ${StartDate})

Try that and let me know if it works!

5

Hi @ryan_illuma, did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the Quick Sight Community!