Pivot function (not table)?

[Margaret]

Does Qlikview support a Pivot command/function/whatever you'd call it?

I want to graph how many doctors of each specialty open the hospital newsletter over several months.

Here's my source data:

Specialty                         Open

Cardiology                         August

Obstetrics                         August

Ear Nose Throat               August

Cardiology                         August

Obstetrics                         July

Cardiology                         September

Here is my desired result set:

Specialty               August          July          September

Cardiology              2                      -               -  

Obstetrics             1                       1               -    

Ear Nose Throat    1                       -               1

How can I do that in Script?

________________________________

Secondly, less importantly, and for learning's sake, I'd like to know if I can do the same thing as a Pivot Table sheet object.

I tried a pivot table with:

Dimension = Specialty

Several Expressions phrased like this, but for different months:

count([Month]='September')

And it looks like it works until you realize that every column has the same numbers as the column next to it. As if each month, the exact same number of doctors in each specialty opened it.

[oknotsen · Accepted solution]

Hi,

I am going to assume you already have QlikView installed.

To do the pivot thing you are asking for, take these steps:

- Create a new chart (right-mouse-click, new sheet object, chart).

- On the first tab (General) that pops up, take the top-right of all the offered chart options

- On the next tab, add Specialty and Month as Dimension

- On the next tab, add count(Doctor) or, alternatively, count(Distinct Doctor)

- Click Finish

The table looks complete folded.

- Click on the tiny plus symbols to unfold the chart (alternatively do a right-mouse-click on the column name and select "Expand All"). Repeat for the next column.

- Click on the "Month" column and keep your mouse button pressed. Now start moving your mouse around; you will notice an arrow. Keep making moves (maybe make some circles) till the arrow changes direction and is in the top-right of the chart. Let go of the mouse button.

You should now have the chart you are hoping for.

To add some (sub-) totals, go into the Properties, to the Presentation tab, and in the top-left turn on "Show Partial Sum".

This should also save you having to make an expression for each month.

[Margaret]

So easy. Just needs a pinch of magic.

I'm off to find a resource called "Things you wouldn't be able to figure out about Qlikview unless somebody told you."

(I'd better take back what I said yesterday about Excel being a better tool for Pivot Tables ;-P)

Thank you, Onno!!

(So there is no command/function/whatever to Pivot on the back end in Qlikview?)

[oknotsen]

Not sure what you mean with "a command to pivot".

There is a way to load cross tables into Qlik, but that seems to be the other way around as what you are looking for.

[Margaret]

How about a function called "Pivot"? (There is one in sql.) It's no biggy now, I don't have anything to use it on, just curious.

[Margaret]

This is really truly great, but.

But I really do need the "function". Or whatever its called. I believe there is one in SQL called "Pivot". Is there one in Qlikview? Or could a crosstable accomplish this?

Because now that I know how many physicians of each specialty opened the newsletter each month, I need the PERCENTAGE of each specialty who opened the newsletter each month. And I imagine I can't do that unless I can actually hold the "number opened" in one place long enough to divide it by the total number of physicians in that specialty.

So, I've added a column to the result set I said I needed before:

Here is my desired result set:

Specialty               August          July          September          TotalInSpecialty

Cardiology              2                      -               -                           3

Obstetrics             1                       1               -                           2

Ear Nose Throat    1                       -               1                           1

And I guess THEN I'd make a pivot table showing

Cardiology              66%                   0               0    

Obstetrics                50%               50%             0

Ear Nose Throat     100%               0               100%

Or do I need to start a new thread to get a flying chance at an answer?

[oknotsen]

Please explain what it does as I do not know the function.

[oknotsen]

I would strongly suggest to always start a new thread for a new question.

To try and answer this question:

Consider the "relative" option, found in the center of the Expression tab (really in the middle of the tab).

Alternatively, you could use something like "sum(Doctor)/sum(TOTAL Doctor)" and show that as a percentage (using the number tab).

[Margaret]

Yeah, except this is really the same question. Because I am still looking for a function that will pivot the months to columns instead of rows.

I actually figured out how to calculate the percentages of providers from each specialty opening the newsletter in each month (using (count([Full Name])/CountSpecialty) and setting it as a percentage in the number tab), so I have those percentages in the pivot table.

But now I want an average % opened all year, and I can't figure out how to get that in the chart, nor in the script, because I think I need to pivot the months still in the script in order to get it, but I'm not sure.

And I can't find what you mean by the "relative" option. I looked in the middle of the Expression Tab in the Chart Properties. But that is for the pivot table. And maybe I really need to focus on the script so I can get the average percentage...

Any ideas?