topic Re: count distinct and sum in QlikView

count distinct and sum

realpixel — Fri, 05 Apr 2013 17:57:36 GMT

Hello,

Under a part of my board

HMCOUNT	CUSTO	TRANS	PEV
1000	CUSTO1	TRANS1	1
1000	CUSTO1	TRANS1	1
1000	CUSTO1	TRANS1	1
2000	CUSTO2	TRANS1	2
3000	CUSTO3	TRANS2	2
3000	CUSTO3	TRANS3	2
3000	CUSTO3	TRANS4	2
4000	CUSTO3	TRANS5	1
5000	CUSTO4	TRANS5	2
6000	CUSTO5	TRANS6	1
6000	CUSTO5	TRANS6	1
6000	CUSTO5	TRANS6	1
7000	CUSTO6	TRANS6	2
8000	CUSTO7	TRANS6	1
9000	CUSTO8	TRANS6	2

I use [TRANS] like dimenssion and function count(distinct HMCOUNT) (I use count distinct because there are several time the same value is HMCOUNT) to do a sum of [PEV], it's works by [TRANS] but how to divide by the total of [PEV] for all [TRANS]?

I want to do sum of [PEV] by [TRANS] divide by the sum all PEV multiply by 100

Re: count distinct and sum

Gysbert_Wassenaar — Fri, 05 Apr 2013 18:02:50 GMT

Try 100*sum(PEV)/sum(total PEV). See attached example.

Re: count distinct and sum

realpixel — Fri, 05 Apr 2013 21:00:58 GMT

Thank you Gysbert for your assistance.

In your example I need to do a sum total of "count(distinct HMCOUNT)"

I want to have a function like that

100*count(distinct HMCOUNT)/ "by sum total of count(distinct HMCOUNT)"

Re: count distinct and sum

kaushiknsolanki — Sat, 06 Apr 2013 06:57:55 GMT

Hi,

Have a look at the attachment.

Regards,

Kaushik Solanki

Re: count distinct and sum

realpixel — Sat, 06 Apr 2013 09:34:05 GMT

Hello,

Thank you for your assistance, it's works.

I have some questions.

1 - I have a sheet for a specific value and I created a new graphic in new sheet and all sheet are in the same document.

When I select a value from the filter on the first sheet, this selection appears also on the other sheet, how to do that the filter selection is independent?

2 - In the new sheet, I created a graphic sheet with sector graphic, I need to display the pourcentage of each sector on the graphic, how to do? The pourcentage appears in the legend but not on the sectors.

3 - In my graphic sector I have same color for [TRANS] and [CUSTO], how to do to have a different color?

4 - I need to display the result only for some value in the column [TRANS]. I use a filter selection to choice the value but I want to fix some value without to use a filter selection. Have you an idea how to do?

Re: count distinct and sum

kaushiknsolanki — Sun, 07 Apr 2013 06:22:16 GMT

Hi,

Ans 1: Yes you can do this with two ways.

1. You place an event on sheet activation to clear the selection when you go to sheet.

Go to sheet properties -> Triggers -> On ActiveSheet Tigger -> Add -> Selection ->Clear All.

2. You use the Alternate state. For more info look into Whats new in QlikView 11.qvw in the example folder of your computer.

Ans 2: I am not sure what you mean to say, please explain in detail with example.

Ans 3: You can click on '+' sign next to the expression -> Expand that you will find the Background color.

Type expression like below in definition space.

if(Field1 = 'ABC',Red(),Yellow())

Ans 4: For selection few values by default you can use the Set Analysis.

Assume you want sales only for country india. Then the expression will be.

Sum({<Country = {"INDIA"}>} Sales)

Regards,

Kaushik Solanki

Re: count distinct and sum

kaushiknsolanki — Sun, 07 Apr 2013 06:22:22 GMT

Hi,

Ans 1: Yes you can do this with two ways.

1. You place an event on sheet activation to clear the selection when you go to sheet.

Go to sheet properties -> Triggers -> On ActiveSheet Tigger -> Add -> Selection ->Clear All.

2. You use the Alternate state. For more info look into Whats new in QlikView 11.qvw in the example folder of your computer.

Ans 2: I am not sure what you mean to say, please explain in detail with example.

Ans 3: You can click on '+' sign next to the expression -> Expand that you will find the Background color.

Type expression like below in definition space.

if(Field1 = 'ABC',Red(),Yellow())

Ans 4: For selection few values by default you can use the Set Analysis.

Assume you want sales only for country india. Then the expression will be.

Sum({<Country = {"INDIA"}>} Sales)

Regards,

Kaushik Solanki

Re: count distinct and sum

realpixel — Mon, 08 Apr 2013 14:00:32 GMT

Hello,

Thank you for reply.

find attached the pie chart, I want to display the pourcentage on the chart and not on the legend, on the both if it's possible.

In use [TRANS] and [CUSTO] like dimension but this value appears in the same color, red. It's possible to have a coleur different, one for [TRANS] and other for [CUSTO].

On the first sheet, I have do a mistake, I have the folling board.

HMCONT	CUSTO	TRANS	DATE MOV	DATE ENTREE	PEV
U6451156	CUSTO1	SV	19/03/2013	10/01/2013	2
U6451156	CUSTO1	SV	19/03/2013	19/03/2013	2
U2520054	CUSTO1	SV	18/03/2013	10/01/2013	1
U2520054	CUSTO1	SV	19/03/2013	19/03/2013	1
U7000870	CUSTO1	SV	19/03/2013	10/01/2013	2
U7000870	CUSTO1	SV	19/03/2013	19/03/2013	2

I use the function =count(distinct HMCONT) to do a sum of column [PEV] result is = 3

But I want PEV 2 = 2 and PEV 1 = 1 the result of the function above must be = 5. Have you an idea to have this result?

Re: count distinct and sum

kaushiknsolanki — Tue, 09 Apr 2013 05:35:35 GMT

Hi,

Kindly upload the sample Qvw file.

Regards,

Kaushik Solanki