I made this change in the Sales Numbers changed with rank sheet. Attached.
Also look at sheet 6. You don't really need the [Sales] calc in the primary data source to display data from the secondary source. Just drag the field from the secondary source right onto the sheet.
test 2.twbx 90.1 KB
Thanks for the prompt response. In our heat map, we actually do want to restart the ranks by row. Due to the weekly seasonality, for example, we want to compare the number of sales for the current Thursday to previous Thursdays, not with Mondays or Sundays. If I don't blend with the place holder dates, a calculation field like this worked great for me:
if not isnull(count([Customer Id]))
then rank(count([Customer Id]))
This way I could force all null values to be rank 0, but the problem is that it couldn't tell the difference between past dates with actual 0 sales and future dates where the sales haven't happened yet (and thus I went with the data blending).
I tried to use [Sales] (primary data source) and [Actual Sales] (from secondary data source) in the view, but neither of them worked right under rank across .
Well I tried something different.
I went into the advanced editor and specified for the table calc (rank) to run through the two dimensions (weekday of patched date, and day of week start). And to restart every weekday of patched date.
That didn't do it. But then I decided to put the actual [Patched Date] dimension on the data shelf, and then add it into the advanced table calc editor. And that did it.
I had messed around with various things. One was to create a string dimension for weekday of patched date. (Used in sheet7.) And that's what prompted me to put the [patched date] dimension on the data shelf.
Turns out it doesn't matter if you use WEEKDAY(Patched Date) or the string calc I made. It turns out that putting the actual [Patched Date] dimension on the data shelf was the trick.
And you'll see that on one sheet I did EXACT DATE on [Patched Date] and on another I just left it as the default YEAR(Patched Date). Either way works. I guess when the default YEAR() goes on, it still has the full data value of the field, but when you have the specific WEEKDAY() as it is in the ROWS shelf, it can only see a piece o the value.
Anyway, now you are getting ranks for the values within a row.
test 3.twbx 103.4 KB
Wow it worked! I have never thought of putting the [Patched Date] dimension on the data shelf and it did the trick.
Thanks again! I'll now play with the actual data and hopefully the data blending will not give me more headaches