13 Replies Latest reply on Dec 7, 2016 10:47 AM by D Barnetson

# Time Series Mapping; color by subset rather than entire dataset

I'm thinking/hoping there is a simple solution to this. I currently have a map of the US that is being colored by state, based on a certain KPI. Now I'm wanting to animate the map based on a time series and everything works except that the coloring is based off the entire data set. The time series is by week, so is there a way I color each state only based on the values for that week and then watch it progress as the time moves forward?

Example:

Entire dataset ranges from 0-100

Each week, each state is close to one another on the 0-100 scale but I want to show how different they are within each week.

I hope this makes sense. Thanks in advance,

Rob

• ###### 1. Re: Time Series Mapping; color by subset rather than entire dataset

Hi Rob,

If it is possible for you to attach a packaged workbook, or a sample data set that would help us to help you much easier.

I have created an example, based on your description. Please have a look at the packaged workbook (v9.0) I have attached.

Let us know if this is what you are trying to achieve.

Don

• ###### 2. Re: Time Series Mapping; color by subset rather than entire dataset

I've attached a simple version. Your example reflects what mine is doing, but not quite what I want.

I'll see if I can clarify some. We'll say for a given week, the state-level data ranges from 21% to 26%. I'd like for that to be the color by range for that week. So 21% would be red and 26% would be green (or whatever color scale I use). What is happening is that the 21% is colored based on the entire data set which might be 0%-100%, so you can't see any difference at all between 21% and 26%.

Does that make sense?

• ###### 3. Re: Time Series Mapping; color by subset rather than entire dataset

Ok... I'm pretty sure I know what you are trying to do.

Create the following Rank calculation:

Rank(sum([Sell-Thru]),'asc')

Place it on Color of the Marks Shelf, then right click on the new pill and select 'Compute Using', then select 'State'.

I'm hoping that you wanted your map to look more like the image attached?

Let me know how it goes.

Don

• ###### 4. Re: Time Series Mapping; color by subset rather than entire dataset

That's a quick workaround, but I think the result is too different from what I need. The 1st image is the output using the ranking methodology, the second image is what it should look like (I manually applied the low and high color thresholds for the same week).

• ###### 5. Re: Time Series Mapping; color by subset rather than entire dataset

Are you able to post the logic you used in your example?

I'd like to see if I can recreate what you have done, but in a way that it allows you to use it the way you are hoping to.

• ###### 6. Re: Time Series Mapping; color by subset rather than entire dataset

Actually there is no logic. I just typed in the min/max for the color-by field.

• ###### 7. Re: Time Series Mapping; color by subset rather than entire dataset

Ok. Actually, that was the idea I had by using the Rank function, was to create a min/max that could be computed using state by week.

There must be something that I'm missing in the way rank looks at the Sell-Thru values.

Which week was your min & max values that you had used in you last post from?

• ###### 8. Re: Time Series Mapping; color by subset rather than entire dataset

I believe it was week 201640

• ###### 9. Re: Time Series Mapping; color by subset rather than entire dataset

I see one issue with the Rank method I had suggested is that the center point is different than using the Sell-Thru values.

I think the best way to approach this is to create a calculated field that finds the range between min to max.

Here is what I have come up with. Create the following calculations:

//MIN Sell-Thru

round(min([Sell-Thru])*100,1)

//MAX Sell-Thru

round(max([Sell-Thru])*100,1)

//Sell-Thru %

round(sum([Sell-Thru])*100,1)

Place the Min & Max on Details of the Marks Shelf.

Place Sell-Thru % on Color of the Marks Shelf, then change 'Compute Using' to 'State'

I've removed AK, HI, DC & PR from 'States' as they were skewing the view, and not present in your example.

Now before moving 'Month' to the Pages Shelf, this is what I get:

...which looks almost identical to you last example you had posted with the values entered manually.

The problem that I have been running into, is when I add Week to the Pages Shelf, Sell-Thru % no longer computes using State.

Still looking into why that is happening.

If anyone else out there has any suggestions Matt Lutton Joe Oppelt Jonathan Drummey

Hopefully we can get this figured out for you soon.

Don

• ###### 10. Re: Time Series Mapping; color by subset rather than entire dataset

Thanks Don. I really appreciate all of the effort.

• ###### 11. Re: Time Series Mapping; color by subset rather than entire dataset

The thing to keep in mind here is that RANK effectively evenly spreads the states out when the goal is to retain the variation in the data while having a distinct scale for each week from lowest to highest. The easiest way to do that is to do a linear scaling for each week, the formula is:

(measure - WINDOW_MIN(measure))

/ (WINDOW_MAX(measure) - WINDOW_MIN(measure))

The compute using will be on State (so it partitions on the Date dimension) and it will return a value between 0 and 1 where 0 is the smallest State value for the date 1 is the largest. Put that on the Color Shelf and then make it into a diverging palette.

If you're just trying to see State-level trends over time then a line chart is much more effective because of the way visual interruptions (like a screen refresh for the Pages Shelf) make us forget what is going on. IMO this kind of view is only good for a big picture perspective.

Jonathan

Sent from my iPhone

2 of 2 people found this helpful
• ###### 12. Re: Time Series Mapping; color by subset rather than entire dataset

Thanks for the reply Jonathan. That sounds like it'll get me where I need to be, however I'm getting an aggregation error for the WINDOW_MIN function. Do I need to put a SUM in front of it?

• ###### 13. Re: Time Series Mapping; color by subset rather than entire dataset

Thanks Jonathan for taking the time to help Rob out.