Variable Rows Card

user07720

I am trying to create a card (table) with 10 different columns highlighting different revenue metrics. In this table I am trying to create 4 rows that aggregate data differently based off of the same column metrics.

 

4 rows:

- Yesterday

- Week to Date

- Month to Date

- Year to Date

 

How would I create a table to facilitate this? Attaching a screenshot of what I would ideally like to create.

Godzilla

What does your current dataset look like? I think you will have to pre aggregate those rows in the dastaset. 

 

 

user07720

The dataset I would be extracting from has historical revenue data from 2016-present. I would utilize this data to calculate Yesterday, WTD, MTD and YTD metrics.

Godzilla

actually you might be able to do it. You'll have to create beast mode fields for all the columns. the first would be something like this:

 

Date Beast Mode:

case when `DateField`=CURRENT_Date()-1 then 'Yesterday'

when WEEK(`DateField`)=WEEK(CURRENT_Date()) and YEAR(`DateField`) and YEAR(CURRENT_Date()) then 'WTD'

when Month(`DateField`)=MONTH(CURRENT_Date()) and YEAR(`DateField`) and YEAR(CURRENT_Date())  then 'MTD'

when Year(`DateField`)=Year(CURRENT_Date()) then 'YTD' end

 

THen do a similar beast mode for each of the metric columns. The only difference will be the THEN statement where instead of the WTD, MTD strings... you will replace it with the metric field. You may have to put a Sum or Count around the Case statement. 

 

user07720

Would a Column View Sumo Card accomplish this?

Godzilla

i don't think so. I don't think there's a way to set different levels of aggregation(WTD, MTD,etc) in Sumo and you cant use Beast mode in Sumo. 

rado98

HI 

 

I dont think the Beastmode above would work as any dates classed as Yesterday will not longer be classed as WTD, and so on.

I think a Beastmode would work but on a transposed version of the card only.

You can use Beastmodes in Sumo, you cannot create them in Sumo though so you need to create in a regular card and share with the data set.

I think agreagation in ETL is probably the way to go to keep the format.