How to properly create future date timeline

user14700

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:

 

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!

 

 

n8isjack

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.

n8isjack

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.