Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Add count to pivot table

Hello,

I tried searching for a post in the forum that might help me but couldn't figure it out.

I want to create a count for my pivot table so that I can see how many refunds was given to an account for a certain reason.

For example:

I added the numbers next to each row that will count how many times adj.amount appears

Untitled.png

Thank you for all your help

16 Replies
sunny_talwar

May be like this?

Capture.PNG

Script:

Table:

LOAD * Inline [

ACCOUNT, DESC, MONTH, VALUE

A, ABC, 01/04/2016, 65

A, DEF, 01/04/2016, 357

B, GHI, 01/01/2016, 1089

C, JKL, 01/01/2016, 29

D, MNO, 01/04/2016, 27

F, PQR, 01/02/2016, 6

F, PQR, 01/02/2016, 1

F, STU, 01/01/2016, 73

F, STU, 01/02/2016, 80

F, STU, 01/03/2016, 108

F, STU, 01/04/2016, 9

];

Dim:

LOAD * Inline [

Dim

1

2

];

Pivot Table

Dimensions:

ACCOUNT

DESC

=Pick(Dim, Month(MONTH), 'Count')

Expression:

=Pick(Dim, Sum(VALUE), Count(VALUE))

Sort Expression for =Pick(Dim, Month(MONTH), 'Count') dimension

=Match(Pick(Dim, Month(MONTH), 'Count'), $(=Concat(DISTINCT Chr(39) & Month(MONTH) & Chr(39), ',', Month(MONTH)) & ',' & Chr(39) & 'Count' & Chr(39)))

Not applicable
Author

I am getting only "December" and "January".

And I don't even have December on my list.

I thought about it, I don't even need to display the count data. I just need to filter those who have a count of more than 3 payments.

Kushal_Chawda

Nice Usage of Pick. Sunny please write a blog on Pick function

Anil_Babu_Samineni

Sunny,

When i open this file the chart showing like below...

217372.PNG

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

You are the one you actually showed me how to use Pick in getting an additional column.

sunny_talwar

Try reloading and see what happens

Kushal_Chawda

Do you want me to write blog then? stalwar1

sunny_talwar

Ofcourse, are you waiting for an invitation?

Kushal_Chawda

I thought you could write better than me as you are frequently using in different scenarios.