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.
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?
time series map.twbx 112.0 KB
Ok... I'm pretty sure I know what you are trying to do.
Create the following Rank calculation:
Place it on Color of the Marks Shelf, then right click on the new pill and select 'Compute Using', then select 'State'.
Adjust your color to 'Red-Green Diverging'.
I'm hoping that you wanted your map to look more like the image attached?
Let me know how it goes.
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.
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?
Let me think about it.
I believe it was week 201640
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:
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.
Hopefully we can get this figured out for you soon.
Thanks Don. I really appreciate all of the effort.
2 of 2 people found this helpful
Answering from my phone...
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.
Sent from my iPhone
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?
Thanks Jonathan for taking the time to help Rob out.