Max Rank by Month and Year for Volume Metric

sobo

Hi,

I was hoping to write a case statement that would allow me to highlight the month with the highest Volume. Additionally, I want to take data from that particular month and I want to graph it by Day.

On the same graph I want to highlight the Volume for the current month as well.

Ideally I would do this in a line chart.

I was hoping someone could help me write this beast mode or guide me in the correct decision.

marcel_luthi

First, what does the raw data looks like? and then if you can do a doodle or something of what your desired visual would be? The reasons I'm asking this is because it seems to me you're talking about 3 different cards which you can put together in a dashboard to show all these entries.

There are also some key things you might want to consider, like the top month overall might be say Feb2023, but if you apply a filter to only a subset of the data this might no longer be true, and March2023 might have a higher volume after the filter is applied, so when you say Month with the Highest Volume do you mean overall or for the specific data you're looking into? This changes whether this needs to be done via ETL or a Beast Mode. Also when you're talking about Highest, is this highest all time or highest on a given window, and if the latter, is that window fixed like Last 6 Months or 1 Year, or is this something you expect your users to be able to adjust via a variable?

Taking a shoot in the dark, you'll need at least 2 Beast Modes for this:

Beast Mode to classify all entries as either "Current Month", "Highest Month" or "Other", this will be used to filter so that you only show entries for the first 2 options.

CASE 

WHEN DATE_FORMAT(`dateColumn`,'%y%m')=DATE_FORMAT(CURDATE(),'%y%m') THEN 'Current'

WHEN <LOGIC TO IDENTIFY HIGHEST MONTH>
ELSE 'Other'

END

One that will tell you the day of the month each date falls into

DAY(`dateColumn`)

With this you'd build your card using the 1st BeastMode as your Series and the second as your X axis with Values being whatever you're measuring (in this case volume).

The only downside with this approach is that if the Current Month also happens to be the Highest, only a single bar/line will be presented.

I'd strongly suggest the flag for highest month to be handled via ETL as there you can use the Rank and Window tile for this purpose and your Beast Mode logic will be kept fairly simple, of course this only works if the Highest should be the global one and not one that is responsive to filters.

marcel_luthi

First, what does the raw data looks like? and then if you can do a doodle or something of what your desired visual would be? The reasons I'm asking this is because it seems to me you're talking about 3 different cards which you can put together in a dashboard to show all these entries.

There are also some key things you might want to consider, like the top month overall might be say Feb2023, but if you apply a filter to only a subset of the data this might no longer be true, and March2023 might have a higher volume after the filter is applied, so when you say Month with the Highest Volume do you mean overall or for the specific data you're looking into? This changes whether this needs to be done via ETL or a Beast Mode. Also when you're talking about Highest, is this highest all time or highest on a given window, and if the latter, is that window fixed like Last 6 Months or 1 Year, or is this something you expect your users to be able to adjust via a variable?

Taking a shoot in the dark, you'll need at least 2 Beast Modes for this:

Beast Mode to classify all entries as either "Current Month", "Highest Month" or "Other", this will be used to filter so that you only show entries for the first 2 options.

CASE 

WHEN DATE_FORMAT(`dateColumn`,'%y%m')=DATE_FORMAT(CURDATE(),'%y%m') THEN 'Current'

WHEN <LOGIC TO IDENTIFY HIGHEST MONTH>
ELSE 'Other'

END

One that will tell you the day of the month each date falls into

DAY(`dateColumn`)

With this you'd build your card using the 1st BeastMode as your Series and the second as your X axis with Values being whatever you're measuring (in this case volume).

The only downside with this approach is that if the Current Month also happens to be the Highest, only a single bar/line will be presented.

I'd strongly suggest the flag for highest month to be handled via ETL as there you can use the Rank and Window tile for this purpose and your Beast Mode logic will be kept fairly simple, of course this only works if the Highest should be the global one and not one that is responsive to filters.

sobo

hello,

I ended up using an ETL to complete the task. Thank you!