Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
amazon_mckenzie
Contributor II
Contributor II

Pivot table 'count' expression

I'm having trouble creating a pivot table chart that takes into consideration the selections I've made.

If, for example, I have the following data set that shows edits made to books:

Edit_Record          Country               Book_Title                    Editor

1                             US                         Book 1                         Jane

2                           UK                         Book 1                         Andy

3                              US                         Book 1                         Jane

4                              US                         Book 1                         Andy

5                              US                         Book 2                         Andy

6                              China                    Book 1                         Andy

7                              US                        Book 3                         Jane

8                              Japan                   Book 2                         Andy

9                              UK                        Book 3                         Jane

10                              Japan                   Book 1                         Andy

In my load script, I included: 1 as Edit_Counter   

Now, I want to create a pivot table chart that has all of the fields above and counts the edits (using the expression =count(Edit_Counter), like so:

Country                Book_Title                    Editor               Edit_Counter   

US                         Book 1                          Jane               2

                              Book 1                          Andy               1

                              Book 2                          Andy               1

                              Book 3                          Jane               1

UK                         Book 1                          Andy               1

                              Book 3                          Jane               1

China                    Book 1                          Andy               1

Japan                   Book 1                          Andy               1

                              Book 2                          Andy               1

But what I am actually getting is the total number of Edit_Counter. My pivot table chart looks like this:

Country                Book_Title                    Editor               Edit_Counter   

US                         Book 1                          Jane               10

                              Book 1                          Andy               10

                              Book 2                          Andy               10

                              Book 3                          Jane               10

UK                         Book 1                          Andy               10

                              Book 3                          Jane               10

China                    Book 1                          Andy               10

Japan                   Book 1                          Andy               10

                              Book 2                          Andy               10

Help!

5 Replies
vishsaggi
Champion III
Champion III

What is your expected output here? Like below?Capture.PNG

amazon_mckenzie
Contributor II
Contributor II
Author

Yes, that's my expected outcome. More specifically, if I select a particular Editor, say Jane, I would only want to see the Edits she did, not all of the Edits by all people.

vishsaggi
Champion III
Champion III

Just use  as below: Pivot table

dimensions: Country, Book_Title and Editor

Expression: Count(Editor)

You get the above table as mentioned in my previous reply.

If you facing any issues can you attach your sample here for a quick look. ?

amazon_mckenzie
Contributor II
Contributor II
Author

Yes- that works... but only for part of my issue, which I think has to do with the fact that I'm measuring more than one thing. Let me change the question just a little- let's assume I am trying to measure how many times someone has edited a particular book.

So if I were to use Count(Editor), that would give me total edits for each person just fine. However, if I use Count( {< Book_Title = {'Book 1'}>}[Editor]), it is still showing me the total edits for each person, not just those for Book 1.

Does this have to do with my load script?

In reality, I have done this a bit differently where I have:

Load

Edit_Record

Country

Book_Title

Editor

Load

Edit_Record

1 as Edit_Counter

vishsaggi
Champion III
Champion III

What is your exact output you are expecting? Can you share pls. You should get the correct values with your expression, ignoring Country field.

Something like this?

Capture.PNG