How do we calculate current day Vs Previous day in Domo.

Salmas

SUM(CASE WHEN MAX(`Day`) = DAY(MAX(`Day`))-1 THEN `Visits` END).

I tried this, but this is not working

jaeW_at_Onyx

@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

https://knowledge.domo.com/Visualize/Adding_Cards_to_Domo/KPI_Cards/Building_Each_Chart_Type/Period-over-Period_Charts

For greater flexibility:

Here's how you can solve with LAG

https://www.youtube.com/watch?v=cnc6gMKZ9R8&t=7s

Here's how you can solve with a Date Dimension

https://www.youtube.com/watch?v=CDKNOmKClms&t=503s

Salmas

Hi, Agree to your point. And that was a mistake.

SUM(CASE WHEN (`Day`) = (MAX(`Day`))-1 THEN `Visits` END)

what about this statement?

GrantSmith

Hi @Salmas

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.

b_rad

Hi @Salmas

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.