Help with creating just one single column containing different time frames

sobo
sobo Member
in Beast Mode

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

Tagged:

Best Answer

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Answers

  • Jenna
    Jenna Member

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

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    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.

Categories