Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On Demand Webinar: See Why Thousands of QlikView Users Have Switched to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Margaret
Creator II
Creator II

Pivot function (not table)?

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.

1 Solution

Accepted Solutions
oknotsen
Master III
Master III

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.

May you live in interesting times!

View solution in original post

8 Replies
oknotsen
Master III
Master III

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.

May you live in interesting times!

View solution in original post

Margaret
Creator II
Creator II

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
Master III
Master III

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.

May you live in interesting times!
Margaret
Creator II
Creator II

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
Creator II
Creator II

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
Master III
Master III

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

May you live in interesting times!
oknotsen
Master III
Master III

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).

May you live in interesting times!
Margaret
Creator II
Creator II

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?