SUM(CASE WHEN MAX(`Day`) = DAY(MAX(`Day`))-1 THEN `Visits` END).
I tried this, but this is not working
@Salmas how do you expect this to behave?
Look at the inside
WHEN MAX(`Day`) = DAY(MAX(`Day`))-1 THEN...
that can NEVER be the same ... because MAX(DAY) can NEVER equal MAX(DAY) -1 .
Try putting your data into EXCEL and figure out how you'd construct the answer...
You could use a Period over Period Chart
For greater flexibility:
Here's how you can solve with LAG
Here's how you can solve with a Date Dimension
Hi, Agree to your point. And that was a mistake.
SUM(CASE WHEN (`Day`) = (MAX(`Day`))-1 THEN `Visits` END)
what about this statement?
You're doing an aggregate of an aggregate (SUM OF MAX) which Domo doesn't like. I'd recommend going with a Date Dimension or LAG function as @jaeW_at_Onyx has outlined above.
I dont think you can do it directly in the chart. You have to prepare your data in the ETL process in such a way you have the current day and prev day values in the same row.
Domo doesnt seem to have a function that would allow us to compare values directly in the chart. Maybe this could be a feature request.
I had to do this for a dashboard I was working on and after trying various things I changed my ETL where I joined my dataset with itself but joined on the previous day. In sql it'll be something like...
select <yourcolumnlist>, table1.value as current_day_value, table2.value as prev_day_value
from "a" as table1 join a as table2 on table1.date-1 = table2.date
Hope this helps.
It looks like you're new here. Those who sign in get access to engage with even MORE fire content. To get involved, click one of these buttons!