Date axis ranges & Predictive

0:00Hey it's Tim here and in today's video I'm

0:01going to be showing you a new feature in

0:032020.4

0:04where Tableau have added the ability to

0:06extend the date access range beyond the

0:08current values

0:09that are actually available in the data set

0:11. Now a caveat before I hop into the video

0:13this only

0:14works in certain scenarios in particular

0:16when you're using predictive modeling

0:18functions,

0:18table calculations like moving average and

0:20running totals and then lastly R and Python

0:23functions

0:23where of course you might be doing some

0:25modeling where you want to look into the

0:27future. So those

0:27are the only instances that it works but

0:29let's hop into Tableau and let me show you

0:30how that works.

0:31Okay I'm going to open up sample superstore

0:34sales here and we're going to just use this

0:36as a basic

0:37data set. I'm going to start by drawing a

0:39chart very simply by dragging sales onto

0:41rows and then

0:41order date onto columns of course by

0:43default it drags a discrete year, discrete

0:46being signaled by

0:47the color blue here and then if I click the

0:49drop down I can make it a continuous month.

0:51Continuous months give us a nice timeline

0:54to work with. Okay so now we have this

0:57chart set up and we

0:58can actually start to play around with this

1:00. Now if you're not aware in 2020.3 they

1:02actually

1:03introduced some of the groundwork for this

1:05feature which is they added the ability to

1:07do things like

1:08infer missing values essentially look at

1:10missing values and essentially read beyond

1:13them or look

1:14at the pattern either side of that missing

1:15value and sort of extrapolate them if that

1:18makes sense.

1:18I'm absolutely maligning that statistical

1:21definition but I'm trying to keep this

1:23video

1:23as simple as I possibly can and so if I

1:25actually go into the month order date you

1:28can actually see

1:28that the show future values feature is now

1:30there. Okay and you can see that you've got

1:32different

1:33time ranges including a custom option. Okay

1:36now if I just go ahead and say one year you

1:38'll see that

1:38nothing happens nothing changes because it

1:40's not a supported calculation that we're

1:42using we're just

1:43looking at sales here it's not a running

1:45total it's not an average and it's not a

1:47predictive

1:47modeling function I definitely haven't

1:49typed anything in front of you. So let's go

1:52ahead

1:52and change this to a moving average and I'm

1:54going to use a quick table calculation here

1:56to select

1:57moving average and when I do that you'll

1:58see that the chart changes and the key

2:00thing to note here

2:01is that it also goes into the future

2:03specifically it goes into February 2021 but

2:06it also shows that

2:07we have 10 nulls okay and if I click on

2:09that I can show the data the default

2:11position and you can see

2:12that it draws the line into the future so

2:14it is it is technically there but it's not

2:16there because

2:17essentially there are nulls there and in

2:19this particular case it's difficult for a

2:21tablet to

2:22do anything because that moving average

2:24needs the values there if you just have the

2:2710 nulls and you

2:28said you know plot them at zero that doesn

2:30't work and if you filter the data well you

2:32're not extending

2:32the date axis range so in this instance

2:35whilst you've extended the data it's not

2:37quite making as

2:38much sense it will make more sense when I

2:40come to the predictive modeling functions

2:43in a second but

2:43I want to show you one last thing notice

2:46that it says 10 nulls and yet we extended

2:49it by a year

2:50well that's because there's a difference

2:53between the table calculation that I'm

2:55using and the

2:56time frame that we've extended you see we

2:58've extended it by a year which is 12 months

3:00and the moving average is actually looking

3:02back at the previous two months and so that

3:05that makes up

3:05for the deficit essentially it says 10 null

3:07s if you add the two that this calculation

3:09looks at

3:10that makes 12 and the reason is is that the

3:12moving average is always looking back at

3:14the previous two

3:15values so it takes two sets of values to

3:18fall to a point where there is no longer

3:19any data if I was

3:21to change this to 12 and set it to zero you

3:23'll see that the nulls disappear here on the

3:25bottom right

3:26and now it's able to compute but now our

3:28moving average is looking at such a broad

3:30time frame that

3:31it doesn't really make sense in the context

3:33of what I'm analyzing especially at a

3:34monthly level

3:35and so we're kind of skewing that maybe

3:37three months might work better but in this

3:40case you

3:40know it just doesn't work that well and you

3:42can see now that I've typed three it's the

3:44deficit

3:44is now nine so it works exactly as you'd

3:46expect you just need to bear that in mind

3:48as you're

3:49working with this so let me set this back

3:51to two as as it was in the default and then

3:53we can just

3:54close this and leave it at that now to show

3:56you the next use case which is actually the

3:58one that

3:58makes the most sense I'm going to reset

4:00this chart a little bit I'm going to go to

4:03sales and I'm going

4:04to clear this table calculation and then

4:06the other thing I'm going to do is I'm also

4:08going to go back

4:09to show future values and I'm going to

4:11select none because in the next option we

4:13're going to choose a

4:14custom value okay let's switch over to the

4:16predictive modeling example okay now we're

4:20going

4:20to look at the predictive modeling options

4:22and I have to sort of preface this which is

4:24predictive

4:26modeling in tableau is one of those things

4:27where you can never really make everyone

4:29happy you've

4:30got the quants who want to know all the

4:32detail and tableau doesn't give it to them

4:33and then you have

4:34the people who you know aren't quants who

4:36use the functions but then aren't quite

4:38happy because they

4:39can't describe what's going on in the

4:40background and so whenever you're working

4:42with predictive

4:43modeling I always urge you to just spend a

4:45little bit more time actually investing

4:47your

4:47investing into your understanding of the

4:49predictive model because if you try and

4:52simplify

4:52the terminology around it actually some of

4:54the analysis you can do is actually quite

4:56simple but

4:57still effective so the predictive model I'm

4:59actually going to be using today is called

5:01the

5:01quantile if I set a quantile of 0.5

5:03essentially what that says is that the

5:05model predicts

5:06that values will essentially fall above or

5:09below this number so at 0.5 that's a median

5:12so you'd

5:12expect an equal spread above and below if I

5:15set it to say 0.9 then I would expect 90 of

5:19our values

5:19to fall below the line and if I set the

5:22quantile to one then I'd expect all values

5:24to fall below

5:25the line okay so that's essentially what I

5:27'm using today now to create this

5:29calculation I'm going to

5:30go ahead and create calculated feel here

5:32and I'm just going to type something so

5:34that I can see how

5:34big the text is and just scale this up so

5:36it's nice and easy to see now the modeling

5:39function

5:39is actually quite easy to find if you were

5:41just to type model you'll see them come up

5:43and I'm using

5:44the quantile as an example here I'll leave

5:47a link in the description to tableau's

5:49documentation on

5:50choosing the right predictive model and

5:52what they do and also the new predictive

5:54models that are

5:55available so let's hit model quantile here

5:57and you'll see that it gives us the

5:59function essentially

6:00and if we click our mouse into that you see

6:03that you get two descriptions okay now we

6:05're going to

6:06just focus on this first description in

6:08another video where I'll touch on the new

6:11models that

6:11are available and so check out that video

6:13or come up in the tooltip above at the

6:15moment so here it's

6:16basically asking what quantile so for this

6:19I'm just going to type in 0.5 then I'm

6:21going to hit

6:22a comma so I'm looking around the

6:23microphone here because it's right in front

6:25of my keyboard

6:26and then the target expression is

6:27essentially the sum of cells the thing I'm

6:29trying to compute so

6:30I'm going to hold command and I'm going to

6:31drag that in the reason I'm doing that is

6:33because I

6:33want to avoid making mistakes okay the next

6:35step is the predictor expression if I hit a

6:38comma you'll

6:38see it moves to the next value here and the

6:40predict expression is essentially going to

6:42be

6:42the date in this instance because it's

6:44basically trying to see look in the coming

6:46months what is

6:47the sum of cells going to be that's sort of

6:49the simplest way to think of it so I'm

6:51going to drag

6:51the order date in here okay and notice when

6:54I do that it actually drags a date trunc

6:56ation function

6:57in there if I wasn't to do that and we just

6:59went ahead and did a normal order date you

7:02get a very

7:02different response because what would

7:04happen is tableau would be doing the model

7:07at the day level

7:07because the order dates are all at the day

7:09level of aggregation right they're not

7:11actually at the

7:12month level and so when we summarize this

7:14up to the month which is what tableau is

7:15doing in the

7:16background it's doing something slightly

7:18different so what we actually specifically

7:21need

7:21is the month so we have to use the date

7:23truncation function here so the way I do

7:25this I just hold

7:26command or control on the windows machine

7:28and drop it in and tableau brings a

7:29function for us because

7:30it understands that that's what has to

7:32happen to get the month from the order date

7:35now if I look

7:35at this I'm already getting an error here

7:37and it says it cannot mix aggregate and non

7:39-aggregate

7:40arguments with this function and in old

7:42school terms if you've used tableau for a

7:44while you'd

7:44know how to fix this essentially you just

7:46need to turn this month into an attribute

7:48because what is

7:49essentially doing is returning a piece of

7:52piece of text and in order to be able to

7:54aggregate text

7:55it's got to become an attribute that's the

7:57best way to think of it so I just type in

7:59attr and it

8:00will do that essentially and of course it

8:02goes and gets rid of my date truncation

8:04which is not what I

8:05wanted so if I just undo that you'll see

8:08that it returns the attr and I can open up

8:10a brackets there

8:12and I now need to put up brackets at the

8:14very end and it looks like we have our

8:16model working okay

8:17the calculation is valid and everything's

8:19correct I'll show you what happens if we

8:20didn't date

8:21truncate okay so let's go call this calc

8:24one hit apply and we're pretty much good to

8:27go now if I

8:27drag calc one onto the canvas with

8:29everything open you can actually see that

8:31that model is now working

8:33really really nicely and okay what I'm

8:34going to do is I'm going to make this

8:36smaller and just move it

8:37to the bottom left here and good old modal

8:39windows and tableaus I can actually leave

8:41this running

8:41here in the bottom left and we can do some

8:43work to this so we can actually make this a

8:45dual axis

8:46chart and we're going to synchronize the

8:49axis and you can see that it's essentially

8:51doing what we

8:52asked for this is essentially the median

8:54value and if you sort of squint your eyes

8:55as I like to do

8:56sometimes with predictive functions you can

8:58just see that that is generally roughly the

9:01center of

9:01the whole data set as that sort of line

9:03goes up it's doing a good job of sort of

9:05showing that and

9:06reflecting that and so there we have it now

9:08to get back to the purpose of this

9:10predictive model

9:11we have to add some values to the date axis

9:14range so let's go ahead and click okay we

9:17're going to

9:18hide this particular header because we don

9:20't need it anymore we have the differences

9:22between the

9:22blue line and we're just going to hide that

9:25then leave it at that now if I go into

9:28analysis you'll

9:28see the infer properties from missing

9:30values is actually ticked this is a

9:32actually quite an

9:33important thing to have ticked because now

9:35if I go to the order day and I show future

9:37values and then

9:38I go to one year you can see that the model

9:41goes well into the future okay what would

9:44happen if we

9:44didn't have that infer missing values

9:47option text let's go ahead to analysis and

9:49untick that and you

9:50see it just draws a flat line so

9:52essentially what tableau is doing is it

9:54gets to that point

9:55it is actually extending the data out which

9:57is why you actually have data points here

9:59but it's stopping flat because it hasn't

10:01got any new information to infer anything

10:03from if we tick

10:04the analysis and then select infer

10:06properties from missing values it takes the

10:09most recent set

10:10of values and just extrapolates that line

10:12all the way through okay and essentially

10:14that starts to

10:15work really really well well we could argue

10:17whether that's the right model or not we

10:18can

10:19come to that point in another video maybe

10:21but essentially you can now see that this

10:23day axis

10:23range extension is working as expected how

10:25far can you push it well that's where the

10:27custom option

10:28goes so if you go to show future values

10:30here then you go to custom you can help

10:32play around with this

10:33toggle and you can keep going until you're

10:36basically happy but at this point you're

10:38using

10:38a very small subset of data and it's just

10:40doing an exponential curve essentially and

10:43that's what's

10:43driving this behavior you can see that

10:45behavior very very clearly okay so it's not

10:47really realistic

10:48so you've got to you still got to apply

10:50some common sense to this don't be choosing

10:52a year

10:52when you don't have a year's worth of sort

10:54of data to work from i think in this

10:55particular case

10:56i think months would make a lot more sense

10:58and if we're maybe choosing three or four

11:00months

11:01that's a much much better sort of medium

11:03point to be settled with and if we were to

11:06just if we just

11:07right click on this and select show filter

11:09we actually see the filters here just below

11:11my face

11:11and now if i play with these filters you'll

11:13see that it's always going to look ahead

11:15three months

11:15however i move this so you can kind of go

11:17back here let's just go back a few months

11:19and there

11:20you go it's always looking ahead three

11:21months and as as the date range gets

11:23smaller you can see that

11:24this line gets like more and more vague it

11:26's kind of not really a good idea and

11:28eventually

11:29this isn't going to work because there's

11:31not enough data in there for it to work so

11:33just bear that in mind if i just click

11:35close and i just expand that back out you

11:37'll see that it

11:38starts working again at this point the last

11:40three values actually get it going

11:42downwards so this

11:43if you're using this you you'd know this is

11:45not a good model because if there's season

11:47ality in

11:47your business and there's not enough data

11:49this is what's going to happen so you

11:52really still have to

11:52apply a lot of common sense to these and

11:54you really have to start to think how they

11:56work

11:56with your data set but you can see here the

11:58main feature here is that the date axis

11:59range is working

12:00as expected and you need to make sure that

12:02you have that feature here to infer

12:04properties from

12:05missing values ticked otherwise you're

12:07going to get a flat line in your predictive

12:09model and it's

12:09not going to work okay so this has been a

12:12long video i apologize it's a weird sort of

12:15mix of

12:15topics to explain but in order to show the

12:17real sort of full use case of date axis

12:20range is being

12:21extended and the video is absolutely

12:22necessary to cover predictive models

12:24because that's actually a

12:25good use case you could also use predictive

12:27models in r and python instead and again

12:30this would apply

12:31the same with running totals and moving

12:33average what might help there is when you

12:35're trying to get

12:36sort of you're trying to solve weird

12:38complex situations where you're moving

12:39averages stopping

12:40not quite where you'd like it or you'd like

12:42to do something slightly different or maybe

12:44you are

12:44actually missing values in sort of your

12:46calculation you don't want sort of some

12:48weird behavior there

12:49that is a slightly different use case maybe

12:51worth doing a video on that some other

12:53point

12:53but just to keep this video focused on the

12:55new feature that's pretty much it if you've

12:57enjoyed

12:57the video you know what to do if you haven

12:59't hit the dislike button twice and send it

13:00to

13:01someone you don't like i'll catch you in

13:02the next video