How to properly create future date timeline

Hey Everyone,

 

So I'm trying to create a visualization of future values based on a selected month. The start month is Feb 2019 and the timeline will go to Feb 2020. My date column from the dataset doesn't go past the current date so I created a calculated field to get the next 365 days:

 

DATE_ADD(`Date`, 365)

 

Then, just to make sure it was working, I put my start month calculated field which looks like this:

 

CASE WHEN MONTH(`Date`) = 2 AND YEAR(`Date`) = 2019 THEN SUM(`VALUES`) END

 

into the analyzer along with the future dates and got this:

image.png

 

The start month value is now populating in Feb 2020 and not Feb 2019. Just to double check, I replaced the Future dates with just the normal Dates field and the Start column populated the correct month and year. How do I fix this?

Thanks!

 

 

Best Answer

  • n8isjack
    n8isjack 🟠
    Accepted Answer

    The trick here is that you updated existing data instead of adding future data. 

     

    The Short Answer:

    You'll have to add values for dates in the future using a dataflow or other data prep process.

     

    The Long Answer: 

    Here is a few sample rows of data

     

    Date        Values
    1/1/2019    10
    2/1/2019    20
    3/1/2019    30

     

     

    Here it is with a new Beastmode: 

    Add_Year = DATE_ADD(`Date`, 365)

    Date        Values Add_Year
    1/1/2019    10 1/1/2020
    2/1/2019    20 2/1/2020
    3/1/2019    30 3/1/2020

     

    Here it is with the second beastmode:

     

    Values_2019 = SUM(CASE WHEN MONTH(`Date`) = 2 AND YEAR(`Date`) = 2019 THEN `VALUES` END)

    Date        Values Add_Year Values_2019
    1/1/2019    10 1/1/2020 0
    2/1/2019    20 2/1/2020 20
    3/1/2019    30 3/1/2020 0

    ###########
    SIDE NOTE: I made an adjustment for a common error that can cause you grief:
    You should put the case statement inside the SUM() rather than the SUM() inside the case.

    So this is dangerous and will miscalculate...
    CASE WHEN MONTH(`Date`) = 2 AND YEAR(`Date`) = 2019 THEN SUM(`VALUES`) END

    And it should be this...
    SUM(CASE WHEN MONTH(`Date`) = 2 AND YEAR(`Date`) = 2019 THEN `VALUES` END)

     

    Finally the result is that the "Add_Year" beastmode has just shifted your data, not created new placeholders for future data. Then by using the original `Date` field in the calculation you only SUM() the values originally associated with Feb 2019.

     

     

    Beastmodes cannot create data that isn't in the dataset, only manipulate existing rows of data.

     

    You have to add the future dates to the underlying dataset.

This discussion has been closed.