date examples are based on writing today - 6th September.
my aim is to create a beastmode, which can ultimately be applied to any metric, which shows the week-on-week % change. this value would be able to be displayed in a table by date(week) or as a KPI card which would be filtered to show data for last week.
the goal is to have the working beastmode for the values which are pre-calculated in the 'period over period' cards. I have attached the vairance bar line example I use - I would like the variance %s shown here as a beastmode value.
example:
a = sum the complete last week revenue (e.g. 26th August - 1st September *Domo weeks run Sunday to Saturday)
b = sum the complete previous to last week revenue (e.g. 19th August - 25th August)
wow%change = sum(a) / sum(b) -1
e.g. 1500 / 1350 - 1 = 11.1%
I found some details on the domo knowledge board but cannot get the formula to work. I get a divide by zero error. example below...
(CASE
WHEN ((year(`activity_date`)=year(curdate())) AND (weekofyear(`activity_date`)=weekofyear(CURDATE())))
THEN `revenue`
END
/
CASE
WHEN (weekofyear(curdate())=1)
THEN (CASE WHEN ((year(`activity_date`)=(year(curdate()) - 1)) AND (weekofyear(`activity_date`)=52))
THEN `revenue` END )
ELSE (CASE WHEN ((year(`activity_date`)=year(curdate())) AND (weekofyear(`activity_date`)=(weekofyear(CURDATE()) -1)))
THEN `revenue` END )
END) -1