topic Re: Add count to pivot table in QlikView

Add count to pivot table

Wed, 18 May 2016 13:20:14 GMT

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

Thank you for all your help

Re: Add count to pivot table

sunny_talwar — Wed, 18 May 2016 17:53:20 GMT

May be like this?

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

];

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

Re: Add count to pivot table

Thu, 19 May 2016 07:13:05 GMT

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

Kushal_Chawda — Thu, 19 May 2016 07:23:49 GMT

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

Re: Add count to pivot table

Anil_Babu_Samineni — Thu, 19 May 2016 07:31:33 GMT

Sunny,

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

Re: Add count to pivot table

sunny_talwar — Thu, 19 May 2016 09:36:24 GMT

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

Re: Add count to pivot table

sunny_talwar — Thu, 19 May 2016 09:36:45 GMT

Try reloading and see what happens

Re: Add count to pivot table

Kushal_Chawda — Thu, 19 May 2016 09:41:01 GMT

Do you want me to write blog then? stalwar1‌

Re: Add count to pivot table

sunny_talwar — Thu, 19 May 2016 09:42:52 GMT

Ofcourse, are you waiting for an invitation?

Re: Add count to pivot table

Kushal_Chawda — Thu, 19 May 2016 09:44:48 GMT

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

Re: Add count to pivot table

sunny_talwar — Thu, 19 May 2016 09:51:15 GMT

May be we can collaborate. I will take first attempt and then you can come in to make modifications or improvements? Deal

Re: Add count to pivot table

Kushal_Chawda — Thu, 19 May 2016 09:57:03 GMT

hahaha ok. done

Re: Add count to pivot table

jagan — Thu, 19 May 2016 11:06:04 GMT

Hi,

Please find attached file for solution.

Script:

Table:

LOAD ACCOUNT, DESC,

Month(MONTH) AS MONTH,

VALUE

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

];

Chart:

Dimensions: ACCOUNT, DESC, MONTH

Expression: =If(secondaryDimensionality(), Sum(VALUE), Count(Distinct MONTH))

Hope this helps you.

Regards,

Jagan.

Re: Add count to pivot table

Anil_Babu_Samineni — Thu, 19 May 2016 11:16:56 GMT

Sunny,

Getting same problem, If possible can you please attach one more time...

Re: Add count to pivot table

sunny_talwar — Thu, 19 May 2016 13:13:49 GMT

Difficult to say without looking at it. Would you be able to share a sample?

Re: Add count to pivot table

Kushal_Chawda — Thu, 19 May 2016 13:50:36 GMT

Remove calculated dimension, apply ok. add it again. and drag it horizontal

Re: Add count to pivot table

jagan — Fri, 20 May 2016 02:46:34 GMT

Hi,

Did you checked my solution?

Regards,

Jagan.