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

AftabMian
AftabMian Member
in Charting

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

Image below is the example:

image.png

The Closest I've gotten is;

image.png

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.

Tagged:

Best Answer

  • jessdoe
    jessdoe Contributor
    Answer ✓

    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.

Answers

  • ColemenWilson
    ColemenWilson Coach

    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…

    If I solved your problem, please select "yes" above

  • AftabMian
    AftabMian Member

    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

    image.png

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

  • jessdoe
    jessdoe Contributor
    Answer ✓

    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
    ColemenWilson Coach

    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.

    If I solved your problem, please select "yes" above

Categories