How are you sorting the PPAs to get the top 10? By Number of Records? Sum(TCK)? CountD(TCK) or something else?
I am not sure I understand your question, but it sounds like you want to first identify the top 10 PPA and then show the trend of CountD(TCK) for the last 4 weeks.
If so, here is what I would do:
1) Create a computed set that identifies the Top 10 PPA based on whatever field you need.
2) Filter based on that set
3) Build your visualization
You may have to do this TOP N selection by formula instead of using one of the aggregations if you need to consider only the last 4 weeks worth of data as opposed to the whole data set. Your formula would need a condition to only include data in the last 4 weeks.
I gave this a try, but I wasn't sure how you want to define last 4 weeks (the tableau relative date way or date > today() - 4 * 7 days).
There may be another way to do this using the condition tab, but this way should work.
Let me know if I misunderstood what you are trying to do.
test.twbx.zip 688.7 KB
First I am sorting by PPA /Number of Records descending order.
Then I do a count on TCK…
I looked at your workbook and I can't believe this is the only way this can be done.
I am wondering if anyone else has any ideas on how to post the TOP 10 PPA's over a 4 week period using a bar chart?
Pedro, I thank you for your time and effort. It's very much appreciated.
I'm not sure I'm understanding your response, is there a way that this solution does not meet your needs? The method that Pedro described would be the highest performing, because the top 10 calc in the Set will be done inside the data source. You could probably do some sort of top 10 index calc, but that would be slower because it requires a lot more data to be returned from the data source to perform the table calculation.
One change to make to the view is to change the WEEK(Date) to be a continuous (green) pill, that way you'll see every week for every ppa.
Nothing is wrong with Pedro’s suggestion I just have to get my head around “sets” which I have never ventured into before.
I was just wondering if there is a way I can do it with the “index” technique? I did the reports originally using the index and rank the top 10 PPA’s using the type of bar chart Pedro used in the TOP PPA ALL WEEKS . When I changed it you use bar charts over a 4 week period using index, I couldn’t get it to work correctly.
Looking at Pedro’s workbook, I notice some of the ppa’s display a 1 week period, 3 week period, and 4 week periods. I know there is data there to consistently display 4 weeks of data (4 bars) for each PPA. So there is an issue there.
Thanks for the feedback from you and Pedro. It’s greatly appreciated.
I'm not clear on what you mean by "consistently display 4 weeks of data". There are not 4 continuous weeks of data for each and every PPA, see the "data is sparse" worksheet in the attached (there are a lot of empty spaces). If you want to be showing the most recent 4 weeks of data for each PPA, then you'd need to either do more work in the SQL to add a new column to identify the week or a complicated set of table calculations.
I set up the Index option in the "workout" and "bar chart" worksheets, the key is for the Rank calc an Advanced Compute Using on Week of Date, ppa (in that order) with a sort on countd/TCK/descending.
test jtd.twbx.zip 724.5 KB