Prevent runningSum/periodToDateSumOverTime values continuing into the future

Q&A
,
1

Is there a way to limit one of the two functions to not continue into the future? I’m looking at Q125 in these two pictures; first is using runningSum and second is using periodToDateSumOverTime. It’s currently WW09. Those that I report to do not want to see the bars continuing to WW13/WW14.

image
image1235×1528 71.5 KB

I’ve tried to flag records <= current date and using an ifelse to return NULL if not flagged for the respective functions. No success.

runningSum(sum({cf_es_desig}),[{header_ww_trunc} ASC])
periodToDateSumOverTime(sum({FULFILLED}), {HEADER_DATE}, QUARTER)

1 Like
2

Hello @tjohnson

Does the last date need to be dynamic from a specific date i.e. Jan1 or “start of this Quarter” and is there date filtering on the sheet that can be manipulated by the user?

First, I would try filtering on the visual using the relative date filter option and have your date window to the exact amount that you want to look forward.

3

The filter is set to the current quarter, Q1’25. The visual doesn’t have a control for the user to change the window. Actuals only go up to the current work week (WW 09). The functions carry the values to WW 10, 11, 12, 13, 14. I don’t want them to.

4

Hey @tjohnson

What type of filter are you using to get it to the first quarter? I’m curious about your date fields and how you have truncated them. Are you using both calculated and non-calculated date fields to build these visuals?

5

All caps = Oracle-side field. Anything else is visual-side in Quicksight via calculated field.

Only filters are HEADER_DATE and HEADER_YEAR. The truncated work week is a string. Ascending order with strings is valid in runningSum, and periodTDSOT uses the Oracle datetime field. Can you explain why you’re focused on the date filters?

6

I have the same suggestion to use the conditions to check with current date. Where or how did you implement this? I am trying to understand why this did not work.

is it something like :
ifelse(header_fin_ship_date<=now(), runningSum(sum({cf_es_desig}),[{header_ww_trunc} ASC]), NULL)

1 Like
7

Thank you, Prantika. Your advice made me revisit the ifelse(), but this time without me trying to incorporate a separate calculated field that flagged values > now(). I created an Excel file that included every work week & the first day of the work week.
ifelse(
max({first_day_of_week}) <= now(), runningSum(sum({FULFILLED}),[{first_day_of_week} ASC])
, NULL
)
Then I just changed the format using 'first_day_of_week" as a datetime field rather than categorical ‘WW xy’.

Thanks!

1 Like