Help with creating just one single column containing different time frames

sobo · 2024-03-27T19:29:19+00:00

There was an error rendering this rich post.

Hi,

I want to create a column that states the time period. I essentially have a dataset that consists of data that is divided across quarter, month and year. For example I have data that displays revenue for the current month, revenue for the current year and revenue for the current day. I want to create column that uses the date/time frame or something down these lines such that I could display the time period.

For example, Revenue for the day of 8th Nov 2023 would be time period day

Revenue for the October,2021 would be month and so on.

I am not sure how to do all this in one column.

I am a bit confused in this matter and was hoping I could get some guidance

Find more posts tagged with

Beast Mode

Accepted answers

marcel_luthi

This sounds like a use case for Variables, I would create a variable to select the time period: Day, Month, Quarter, Year, etc. and then a second variable asking for a date in the period.

This way if the date entered is 2/17/2024 then depending on the first variable (time period) you'd get:

Day: only the entries for 2/17/2024
Month: entries from 2/1/2024 to 2/29/2024
Quarter: entries from 1/1/2024 to 3/31/2024 (or whatever your fiscal calendar might be)
etc.

This will be done by creating a Beast Mode that will be used as a filter in your card (the date range should be set to show All Time), something like:

CASE WHEN `time period` = 'Day' AND `dateCol` = `reference` THEN 'true'

WHEN `time period` = 'Month' AND DATE_FORMAT(`dateCol`,'%Y%m') = DATE_FORMAT(`reference`,'%Y%m') THEN 'true'

…

ELSE 'false'

END

You'll need to build a case statement for each possible time period you create, but something like this should work.

All comments

Jenna

Can you give an example of what you are wanting your dataset and new column to look like?

marcel_luthi

This sounds like a use case for Variables, I would create a variable to select the time period: Day, Month, Quarter, Year, etc. and then a second variable asking for a date in the period.

This way if the date entered is 2/17/2024 then depending on the first variable (time period) you'd get:

Day: only the entries for 2/17/2024
Month: entries from 2/1/2024 to 2/29/2024
Quarter: entries from 1/1/2024 to 3/31/2024 (or whatever your fiscal calendar might be)
etc.

This will be done by creating a Beast Mode that will be used as a filter in your card (the date range should be set to show All Time), something like:

CASE WHEN `time period` = 'Day' AND `dateCol` = `reference` THEN 'true'

WHEN `time period` = 'Month' AND DATE_FORMAT(`dateCol`,'%Y%m') = DATE_FORMAT(`reference`,'%Y%m') THEN 'true'

…

ELSE 'false'

END

You'll need to build a case statement for each possible time period you create, but something like this should work.