How to Create WTD, MTD, QTD, YTD on one Chart

AftabMian

I received a request to create a chart to show KPI with WTD … YTD on a single chart.

Image below is the example:

The Closest I've gotten is;

I created a dimeson that flags the 'WTD' dates as a Y/N, then created a beast mode doing a case statement for when the Date flags are 'Y.' I've done the same for the other KPI, but it will just make the table wide instead of being able to place them on separate rows

I don't know how to add the separate KPI on different rows.

jessdoe

You can accomplish this at the ETL level. You'll need transforms for the WTD, MTD, QTD, YTD ranges along with calculations in each of those transforms aggregating your measurable fields.

ColemenWilson

Last Week:
CASE WHEN WEEK(`DateField`) = WEEK(CURRENT_DATE())-1 THEN 'In' ELSE 'Out' END
MTD:
CASE WHEN `DateField` > LAST_DAY(CURRENT_DATE()) - INTERVAL 1 MONTH AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' END
QTD:
CASE WHEN QUARTER(`DateField`) = QUARTER(CURRENT_DATE()) AND YEAR(`DateField`) = YEAR(CURRENT_DATE()) AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' END
YTD:
CASE WHEN YEAR(`DateField`) = YEAR(CURRENT_DATE()) AND `DateField` <= CURRENT_DATE() THEN 'In' ELSE 'Out' END

Etc…

AftabMian

Hey @ColemenWilson,

Thanks for the reply, but I think the Beast mode makes sense, but I cant position the different KPI on Separate rows. The request is to get WTD sales in Column 1, and Sales on Row 1, and Plan on Row 2

I'm currently using the mega table visual, and can't figure out how to break out the KPI by Time slice

jessdoe

You can accomplish this at the ETL level. You'll need transforms for the WTD, MTD, QTD, YTD ranges along with calculations in each of those transforms aggregating your measurable fields.

ColemenWilson

Re-read your initial question - you would need to do some work in ETL as @jessdoe pointed out. You can re-purpose your beastmodes to save time. Just use the formula tile in magic.