Pie Graph only showing most recent Fiscal Year of data
I am trying to develop a pie graph which shows only the most recent selected year of data, in a selected range of fiacal years.
On the first sheet I can select the main organization and the fiscal years to report on from list tables.
On a subordinate sheet I have a pivot table showing injuries by job classification over the selected fiscal years. I have a bar graph showing the same data in graphical format, but restricted to the top 10 job classificaitons with the highest injuries over the range of fiscal years.
Now I need a pie graph showing the injuries from the most recent fiscal year selected. I have searched for previous posts and read through the support manual and have not had any success. I have been using Qlik view for 3 days now and have very little experience. Please help.
OK. I created your example and it works great. I even playted with "count" instead of "sum" as that is what I am doing in my project and it works thay way too.
Next, I tried it in my project and it seems to be working great. The only pitfall is if there are a lot of items ranked 7-18 as they all have the same value. Then it returns all those matching rows. I can lower the rank to <=6 but then I have to change that each time the rptOrg changes as the counts will not be the same. Any suggestions for that?
Also, any way to force the "Other" to always be at the bottom?
My suggestion is to go ahead and display everything ranked 7-18. If there is no difference between them, why would you include some but not others? And if there IS a difference between them, then it should be part of your ranking function, and thus you'd only display ten.
I'm not sure how to force 'Other' to be at the bottom here. I had a couple ideas, like using a dual() function to assign numeric values to each distinct value of X, and then assign 99999999 to 'Other', and then enter a sort expression to try to manage the sort manually. That didn't work. I tried creating an extra dimension of 'Top 10' and 'Other', but couldn't work that out either. Might be able to remove the 'Other's from the main data, then create a total line, but override 'Total' with 'Other' and override the expression on the total line to sum up things ranked higher than 10. Didn't even try that. Anyway, it looks ugly. Maybe someone has a clever solution, but I don't, particularly when even if I can make it work, I can't post it and I'm forced to try to describe what I did.
A general suggestion would be to, at least while a beginner, spend more time trying to figure out how the product wants you to display information instead of how to force the product to display information the way you want to see it. Start simple. Maybe there's a better way to display what you want.
In this case, for instance, a straight table might do what you want, since a straight table supports a maximum number and an Others row directly. Hmmm, for some reason it isn't working for me, though. But anyway, that's what I mean about trying to figure out how the product wants you to display it.
Wow, and double WOW. Thaks for your help. I know this stuff can suck all the time out of a day.
You are right that I should list all similarly ranked data. I was just concerned about space on the report page. I tried the simple table but I really need the data in a pivot table showing the breakdown for each fiscal year for each row of data. I am surprised at how hard it is to sort the data by total frequiency (sum or count) when adding the second dimension of a fiscal year.
