KPI to compare Quote this year with last year

Screenshot 2024-03-14 at 14.07.20
I need to create a KPI to compare Quote this year with last year.

I used for the first column this formula:

sum({YTD_Aktivierung})/sum({YTD_Registrierung})
where
YTD_Aktivierung = ifelse(dateDiff(datetime,${AsOfDate},"YYYY") = 0 AND truncDate('DD', datetime)<=${AsOfDate}, ifelse(eventtypetitle='new-user-account-confirmed' , cnt, 0), 0)

YTD_Registrierung = ifelse(dateDiff(datetime,${AsOfDate},"YYYY") = 0 AND truncDate('DD', datetime)<=${AsOfDate}, ifelse(eventtypetitle='new-user-account-registered', cnt, 0), 0)

for the second column:
sum({YTD_Aktivierung_last_year})/(sum({YTD_Reg_last_year}))
where
YTD_Aktivierung_last_year = ifelse(dateDiff(datetime,${AsOfDate},"YYYY") = 1 AND truncDate('DD', datetime) <= addDateTime(-1,"YYYY",${AsOfDate}),ifelse(eventtypetitle='new-user-account-confirmed' , cnt, 0),0)
YTD_Reg_last_year = ifelse(dateDiff(datetime,${AsOfDate},"YYYY") = 1 AND truncDate('DD', datetime) <= addDateTime(-1,"YYYY",${AsOfDate}),ifelse(eventtypetitle='new-user-account-registered', cnt, 0),0)

I try to get the third column “Result” for KPI as:
{YTD_Aktivierungsquote_last_year}+{YTD_Aktivierungsquote}, but I got Null.

I’m a bit stuck, how do I get the resulting sequence of values that y could display as KPI?

Thanks in advance

1 Like

Hello @Ekaterina, I have a few questions. You are saying you want it in a KPI, but the visual and your explanation sound more like a pivot table. Either way, you can accomplish this in an easier way. Both of the visuals will aggregate data by default since they can be managed by the date field, so you do not need to check what year the data is linked to. The only important piece of the ifelse statements you need to keep would be the part dictating which eventypetitle you are looking at.

I would create 2 calculated fields to make sure you are using the correct fields based on your expectations:
Aktivierung = ifelse(eventtypetitle='new-user-account-confirmed', cnt, 0)
Registrierung = ifelse(eventtypetitle='new-user-account-registered', cnt, 0)

Then you can run both functions partition on the year to get the percent value:
sumOver({Aktivierung}, [truncDate('YYYY', {datetime})], PRE_AGG)/sumOver({Registrierung}, [truncDate('YYYY', {datetime})], PRE_AGG)

Now, in your KPI or Pivot Table, that field can be utilized, and you will want to add your date field and make sure it is aggregated by year. Then you should start seeing values closer to your expected result. I will mark this as the solution, but let me know if you have any further questions!

1 Like

The one edit to Dylans response would be to change registered to confirmed
Aktivierung = ifelse(eventtypetitle='new-user-account-confirmed', cnt, 0)

Thank you.

2 Likes

Hello @Asem, thanks for double checking that! I’ll edit above.

1 Like