Pie Graph only showing most recent Fiscal Year of data

[#1]

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.

Thanks in advance.

[johnw]

Well, let's say your pie chart has an expression like this:

count(distinct InjuryID)

Assuming you have a Year field (you should), to get only the most recent selected year's injuries, I think it would be something like this:

count({<Year={$(=max(Year))}>} distinct InjuryID)

Though depending on the definition of your Year field, it might be something like this instead:

count({<Year={'$(=date(max(Year),'YYYY'))'}>} distinct InjuryID)

Not exactly intuitive, I know, and I don't guarantee that I have the exact right syntax. For more information, search for "set analysis" in the help.

[#3]

Thank you for the response.

We actually are using strings for the fiscal year (ex. "2007/2008" ) instead of just a single year as they don't run on a calendar year. I was thinking I would have to bite the bullet and add another field with just a single year to represent the beginning year for eacy FY. This would be easy to add and greatly simplify things and allow me to try your code.

Thanks. I will post again in a couple days when I get time to make the data changes and test.

P.S. Pardon my ignorance, what is the "distinct" for? It is working without it. (Actually it is all text fields in a single table and no ID or relations are used--rather archaic I know.) I am pulling the data from a query in an Access database so a lot of info is consolidated and crunched when impported to Qlikview. I know all this could be done internally to Qlikview if I had the time and will to learn how to do the programming but I just don't have the time right now. I really should take some classes.

[johnw · Accepted solution]

You don't need to change from a string to do what I was suggesting, because your strings will also "sort" in the correct order. So you could probably do something like this:

count({<Year={'$(=maxstring(Year))'}>} InjuryID)

I used "distinct" before because I didn't know your data layout. If one injury can appear more than once in your table, using distinct would only count it once as I suspect would be desired. If the same ID appears in more than one table, using distinct is often required to get the right count. But it sounds like you have a single table with one row per injury, in which case the distinct isn't necessary.

While likely not necessary here, also worth noting is that you can have a single field with both numeric and text values using the dual() function. For instance:

dual('2007/2008',2007)

So even if you wanted a numeric start year, you wouldn't necessarily need a new field, you could just associated the numeric start year with your text fiscal year in a single field.

There's also nothing wrong with pulling in and using consolidated data directly. There's no need to then split it apart into separate tables. While QlikView deals just fine with highly-normalized data structures, it also deals just fine with highly-denormalized data structures, and most of the down sides of denormalization are avoided since it's a read-only application.

[#5]

I finally got a chance to try your code and it worked perfectly. Thank you for your help.

Another question is how to combine the text "For FIscal Year:" and =maxstring(FiscalYear) as text in a pie graph.

[johnw]

Probably this?

='For Fiscal Year: ' & maxstring(FiscalYear)

[#7]

Well, that worked great too. Thank you.

I realize this is a little off topic, but I have two additional areas where I need help.

1) How to restrict a pivot table to a limited number of rows. I would only like to display the first 10 rows as I have sorted them. And if possible have the remaining rows as "other" , like they way the pie graphs and bar graphs can do it.

2) I have taken everything and placed them into sheets in a report. The report is banded by a field "rptOrg" and it works great in print preview. Now I need to generate a set of PDF files, one for each rptOrg, and need to put the organization name into the file names. Could you perhaps also help with this too?

Thank you very much for your great help so far. I really appreciate it.

[johnw]

Attached is a simple example for the first question. In this case, my expression is sum(Y), which I'm sorting descending. If I want X as my dimension, I instead use this calculated dimension:

=aggr(if(rank(sum(Y))<=10,X,'Other'),X)

As for reports, I don't use them, so won't be much help.

[#9]

Thank you for posting your reply so quickly. Unfortunately I am unable to open the file. I get an error message about opening a file created by another Personal Edition version of the software.

I tried the "rank" but I just don't know enough about the syntax to do the coding. I'm afraid I am a rank amateur at this stuff.

[johnw]

Unfortunately, you can't open someone else's files in Personal Edition, so you won't be able to see any examples I post. So we're probably reaching the limits of how much I can help you. Still, this one example is simple enough you can probably duplicate exactly what I did. Here's my script:

LOAD * INLINE [
X,Y
A,5
A,5
B,5
C,7
D,8
E,10
F,4
G,4
H,8
H,9
I,9
J,4
K,1
L,2
];

Then create a new pivot table. Instead of adding a regular dimension, click on the "Add Calculated Dimension" button. Enter this expression:

=aggr(if(rank(sum(Y))<=10,X,'Other'),X)

And on the Expressions tab, enter this expression:

sum(Y)

And on the Sort tab, tell it to sort by Y-value descending. I THINK that's all I did in the example. So hopefully that gets you a working example. Then I'd pull up the QlikView help and search for "aggr" and "rank", since those are the main things I'm using here.