PeriodToDateSum If/Else for past this month only?

Question & Answer
, ,
1

I’m working on Something that I can’t quite figure out.

Basically, I have a data set that looks like this:

– Date: Just Dates for the last two years
– EOM Date: The last day of the month for the above Date
– Apps: The number of applications received.

I’m trying to create a table that looks like this:

  1. EOM DATE,
  2. A column that is for the Current Month, “Month To Date Apps” and Previous months just the total Apps,
  3. A column that is just like Column 2 but For Last Year
  4. The % Change for Columns 2 and 3 YoY

Column 2 I think is basically just:
periodToDateSumOverTime(sum(Apps),Date,MONTH)
That seems to give me the expected result.

I’m not sure how to create a version of that that is offset by one Year… and then also not sure how to do the Period over Period Difference that takes that into account also.

Any ideas?

2

QuickSight has pretty good understanding of dates and date-aware functions. Nice work sorting out the sum over time! Note Q in Quicksight generated my calculated fields by asking for "previous year’s value of " with your formula and it suggested:

periodOverPeriodLastValue(
    periodToDateSumOverTime(sum(Apps), {Date}, 'MONTH'),
    {Date},
    'YEAR',
    1
)

Similarly, the YoY% function just needs your formula to use for the comparison:

periodOverPeriodPercentDifference(
    periodToDateSumOverTime(sum(Apps), {Date}, 'MONTH'),
    {Date},
    'YEAR',
    1
)

This would be even easier if we could nest custom fields, but I think this is pretty manageable.

There’s a great blog about comparative and cumulative date functions if you’re interested in more.

Hope that helps!

1 Like
3

John, thank you! This is very helpful, but I think I have one last issue I can’t quite sort:

So I’m using this formula:

periodOverPeriodLastValue(
periodToDateSumOverTime(sum(Apps), {Date}, ‘MONTH’),
{Date},
‘YEAR’,
1
)

And for the YAGO value, it’s actually giving me the FULL month.
i.e. so for July 2025, MTD Apps gives me ~5K, MTD Apps YAGO gives me ~120K because it’s capturing ALL of the apps from July 2024, and we are only 1 day in (data is delayed a day).

Any thoughts on a workaround?

4

@TRube Reading to the end of the blog, it seems there are some limitations here. See " Advanced use case 3: Partial period comparisons" stating " This requires a calculated field using periodOverPeriodDifference nested with the sumIf() window function." The example shows a quarter but you can adapt to a month easily.

Do you have a little public/synthetic dataset you could share here or in the arena, along with expected results?

5

Unfortunately, I can’t upload data to Arena… I just Tried.

But Picture data that looks like this:

Date Apps
1/1/2024 1
1/2/2024 1
1/3/2024 1
1/4/2024 1
… …
7/1/2025 1

in this instance, I just put 1 app for every day, for simplicity’s sake.

The result I want from that on 7/2/2025 is:

Date Agged to Month YTD Apps YTD Apps Yago
Jan 2024 31
Feb 2024 60
… … …
Jan 2025 31 31
Feb 2025 59 60
…
Jun 2025 181 182
July 2025 182 183

Note that 2024 is a leap year, so that’s why the numbers get out of sync.

Does that make sense? The version that I currently have, I end up with July 2025 YTD Apps YAGo Counting all apps from 2024 through end of July 2024.

6

I think I figured out a solution… even though I will say I don’t love it because I think it mightfail if I ever go back to earlier than just the start of the prior year… but I will worry about that if it happens :wink:

Essentially I created two self explanatory relative date parameters:
StartOfThisYearDate
YesterdayDate

Then I created an intermediary calculated Field that I only use in calculation.

I called it LastYearSumIfApps:
sumIf(
Apps,
Date <= addDateTime(-1,‘YYYY’,${YesterdayDate})
)

Basically it will sum all apps, but ONLY if they are from a date earlier than at least 1 year before yesterday, so if you look at the 2024 data daily, this field would sum up all the apps for each day, but be null for 7/2/2024 and after (it is 7/2/2025 today).

Then to create YTD Apps YAGO I did:
periodOverPeriodLastValue(
periodToDateSumOverTime({Last Year SumIF (Apps)},Date,YEAR),
Date,
‘YEAR’,
1
)

I could combine them, but I did it in parts because I kept getting formatting errors from all the nested parentheses and was having trouble figuring out because the error messages are kind of bad in QS once you start nesting functions.

Thanks for the help though! Your post definitely helped me get on the right track.

1 Like