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