Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Add count to pivot table

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

Re: Add count to pivot table

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.

Re: Add count to pivot table

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

Re: Add count to pivot table

Sunny,

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

217372.PNG

Life is so rich, and we need to respect to the life !!!

Re: Add count to pivot table

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

Re: Add count to pivot table

Try reloading and see what happens

Re: Add count to pivot table

Do you want me to write blog then? stalwar1

Re: Add count to pivot table

Ofcourse, are you waiting for an invitation?

Re: Add count to pivot table

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

Community Browser