Quarterly Chart

domoexpert

I am trying to create a line chart and graph it by Quarters but the only thing is that instead of summing the values or taking their average, i want to only take values for the last month of the quarter.

e.g For 2022 q1 it would be march 2022 , q2, june etc

Ashleigh

@domoexpert can you create a beasemode that determine if its the last month in the qtr and then filter for that? Something like this should work.

Case when monthname in (March, June, Sept, Dec) then 'Last Month in Qtr' else 'Not Last Month in Qtr' end

Then apply a fillter selecting Last Month in Qtr.

Ashleigh

@domoexpert can you create a beasemode that determine if its the last month in the qtr and then filter for that? Something like this should work.

Case when monthname in (March, June, Sept, Dec) then 'Last Month in Qtr' else 'Not Last Month in Qtr' end

Then apply a fillter selecting Last Month in Qtr.

GrantSmith

You can do this with a window function:

MAX(FIRST_VALUE(`value_field`) OVER (PARTITION BY YEAR(`dt`), QUARTER(`dt`) ORDER BY `dt` DESC)

This will get you a single value of the first value for each year+quarter partition. Since it's ordering by the dt field it'll get you the last date in the quarter.

marcel_luthi

I guess this mostly depends on whether your data is already including only a single entry per month, or if you have multiple entries in each month that need to be totalized monthly and then that be displayed. Another question would be what you expect to see if lets say Jan and Feb have values but March does not (this might not be a concern if your data is already normalized and cleaned for you to use), @GrantSmith approach will retrieve the latest one where an entry for that quarter exists.

@GrantSmith thanks for the pointer on the FIRST_VALUE function, this will solve many things I'd been struggling with 😅