count distinct and sum

[realpixel]

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

[Gysbert_Wassenaar]

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

[realpixel]

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

[kaushiknsolanki]

Hi,

     Have a look at the attachment.

Regards,

Kaushik Solanki

[realpixel]

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?

[kaushiknsolanki]

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

[kaushiknsolanki]

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

[realpixel]

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?

[kaushiknsolanki]

Hi,

     Kindly upload the sample Qvw file.

Regards,

Kaushik Solanki