YtD KPI aggregation by year

[JRoz]

Hi community! I have a problem that I need your help with

I need Year to Date KPI analysis - I have created a calculated field for that using ifelse() - f.ex. if today is 12.09.2024 the values last year to that date last year (so before 12.09.2023) will be X, else will be NULL - to summarize:

• 01.01.2023 : 12.09.2023 → X
• 12.09.2023 : 31.12.2023 → NULL
• 01.01.2024 : now() → X

Then I am creating my KPI in the dashboard, everything looks fine until I aggregate by year (to compare YoY values) - because QS is calculating an average of last year, treating missing months values as 0 - and that’s lowering the average. Is there a way to fix that? Would be grateful for any help!

PS: By aggregating by year I mean aggregating the Trend Group with date

[duncan]

Hello @JRoz !

I’m not sure how your ifelse function is working. Can you share what the calculated field looks like in your analysis?

[JRoz]

No problem, it looks sth like this:

ifelse(
  {DATE} < addDateTime(-1, 'YYYY', now()) OR extract('YYYY', {DATE}) = extract('YYYY', now()) AND extract('YYYY', {DATE}) >= extract('YYYY', now()) - 1,
  {X},
  NULL
)

[duncan]

Hello @JRoz !

Do you ultimately want your users to be able to able to switch between YoY or MoM?

If you just want to do this by year and have it be dynamic, I would recommend using a solution similar to the one below:

Dynamic YTD Calculation depending on todays date for a count of Number of Events

Hi, I’ve got a dataset that includes the number of events that happen in a year. For a year on year comparison I would like to only include the “Year to Date” data for every year depending on today’s date. e.g. if Today’s date is 21st of August 2023. I would only want to include events that are in the dataset for every year that are before 21st of August 2022, 21st of August 2021, 21st of August 2020 etc. I’ve included examples in the screenshots below to illustrate what I’m looking for: [i…

[duncan]

Hey @JRoz!

Were you able to try the solution a linked above?

[JRoz]

I don’t know if that was a problem with formula, but rather with the KPI - I wanted to have average on that value, and it treated missing data from months from now to end of the year as 0 (because they were nulls) but found a way by applying a filter to exclude nulls and not treat the nulls like 0 nevertheless thank you for your help! Much appreciated