For each metric I should make calculation of numerator/denominator so my approach should be like:
Numerator now / Denominator now = Output now
Numerator last week / Denominator last week = Output last week
Finally WoW calculation.
I did something like this but It does not seem logical to me sum(ifelse(({report_weeks} = extract(‘WD’, adddatetime(-7, ‘DD’, now()))) and ({report_years} = extract(‘YYYY’, adddatetime(-1, ‘MM’, now()))), numerator, null))
Did you receive any errors with the calculated field that you typed out above or do you not like the syntax? I think for the way your data is set up, that ifelse function makes sense to grab the value you want to compare. You could also consider using something like the lag function to pull in your desired metric value based on a time frame, but that would likely require similar functionality since you don’t have a standard date field.
Let me know what errors you are experiencing, and we can try to dig into this a little further. Otherwise, if it works, I would stick with the functionality you provided.
Thanks for the answer Dylan! Actually it is giving syntax error , I am still trying it by creating new column with dd/MM/yyyy mapping that I have created.
Hello @Ozzy, I am thinking you might be receiving a syntax error because your report_years and report_weeks fields are not the same value type as the extract/adddatetime function.
What if you convert each to a string then compare them? Then, you can even remove the sum around your ifelse statement and aggregate the returned numerator as sum when you add it to your table. That should simplify the function a bit and fix syntax issues.
This is what I am thinking: ifelse((toString({report_weeks}) = toString(extract(‘WD’, adddatetime(-7, ‘DD’, now())))) and (toString({report_years}) = toString(extract(‘YYYY’, adddatetime(-1, ‘MM’, now())))), numerator, null)
If that works, feel free to mark my response as a solution. Otherwise, let me know if you run into any more errors. Thank you!
, I am still trying it by creating new column with dd/MM/yyyy mapping that I have created.