How can I display amounts for different months in a table in Domo?

BryantCafferty · 2023-12-12T17:32:37+00:00

There was an error rendering this rich post.

I want each row to contain the amount for the previous month as well as the amount for each of the 5 months prior to that. What is the best way to do this in Domo with a Table chart type?

Find more posts tagged with

Date

Table

Accepted answers

MarkSnodgrass

Not quite sure what your data looks like, but

You can use this to get the prior month

CASE WHEN LAST_DAY(datefield) = LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month)) THEN totalamount

ELSE 0 END

And this to get the 5 months prior

CASE WHEN LAST_DAY(datefield) <= LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 month))

AND LAST_DAY(datefield) >= LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 6 month))

THEN totalamount

ELSE 0 END

You can drag both of these into your table card and set the aggregation to Sum

All comments

GrantSmith

You can use the LAG function to calculate prior row values. Here's a previous post I did talking about LAG and doing a rolling average:

https://community-forums.domo.com/main/discussion/52679/domo-ideas-conference-beast-modes-rolling-averages

MarkSnodgrass

Not quite sure what your data looks like, but

You can use this to get the prior month

CASE WHEN LAST_DAY(datefield) = LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 1 month)) THEN totalamount

ELSE 0 END

And this to get the 5 months prior

CASE WHEN LAST_DAY(datefield) <= LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 2 month))

AND LAST_DAY(datefield) >= LAST_DAY(DATE_SUB(CURRENT_DATE(), INTERVAL 6 month))

THEN totalamount

ELSE 0 END

You can drag both of these into your table card and set the aggregation to Sum

BryantCafferty

@GrantSmith for the win on speed of reply - awesome! Thanks for jumping on this (my first post to the community forum!).
Also incredibly fast was @MarkSnodgrass who delivered what I was looking for. Hooray!
Thanks to you both for helping me out with this one. I'm super impressed with the speed and precision of your replies! 👏