topic Re: Pivot Table and Set Analysis? in QlikView

Pivot Table and Set Analysis?

Fri, 02 Aug 2013 03:10:43 GMT

Hi everyone,

I am trying to create a report that counts in a certain way, and not sure how to go about doing that. I might have used a wrong title here, but here goes...

Suppose I have the dataset below, and I would like to create a report that calculates total values as well as counts the number of IDs whose value_1 is greater than value_2.

I am having trouble counting the number of IDs. My desired result/report is also shown below. Thank you.

DATASET:

YYYYMM	ID	Value 01	Value 02
201303	1	1	29
201303	2	92	88
201303	3	58	40
201303	4	91	50
201304	1	40	47
201304	2	64	46
201304	3	51	96

Desired Result/Report:

Total Value 1	397
Total Value 2	396
Total # of IDs	4
# of IDs: Value 1 > Value 2	2
# of IDs: Value 1 <= Value 2	2

Re: Pivot Table and Set Analysis?

Kushal_Chawda — Fri, 02 Aug 2013 04:50:56 GMT

See the attched application

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 05:16:05 GMT

Hi Kush,

thank you for the app, but I don't think it does what I need yet. The number of IDs should be tied to to the (total) time period selected.

In this example, when ID = 3 is selected, Total Value 1 = 109 and Value 2 = 136, and so the report should display

(# of IDs: Value 1 > Value 2) = 0
(# of IDs: Value 1 <= Value 2) = 1

Could you assist?

Re: Pivot Table and Set Analysis?

Kushal_Chawda — Fri, 02 Aug 2013 05:21:43 GMT

Hi ,

see the attched one

f I am not wrong ...# of IDs: Value 1 > Value 2 =1 not 0

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 05:36:54 GMT

I am not saying that you are wrong, I am saying that the app is not displaying what I need:

When ID = 3 is selected, I am seeing

Total Value 1 = 109
Total Value 2 = 136
Total # of IDS = 1

which is fine.

However, I need to display

# of IDs: Value 1 > Value 2 = 0
# of IDs: Value 1 < Value 2 = 1

I get this result when I select 201303 and 201304 and ID = 3. Value 1 are 58 and 51, while Value 2 are 40 and 96. And when we sum up the values and compare total value 1 and total value 2, we get 109 vs 136 which yields my desired result.

Re: Pivot Table and Set Analysis?

Kushal_Chawda — Fri, 02 Aug 2013 06:08:55 GMT

Hi,

Got your point..

see the attached one

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 06:09:10 GMT

Thank you for the app, but it still doesn't provide what I need.

I need to count distinct IDs over entired period selected. In app, when I remove YearMonth dimension and select ID = 3, it displays below

ID For Value01 > ID For Value02 = 1
ID For Value01 < ID For Value02 = 1

However, I would like to display

ID For Value01 > ID For Value02 = 0
ID For Value01 < ID For Value02 = 1

because Total Value 01 and Total Value 02 for ID=3 is 109 and 136 respectively.

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 06:58:49 GMT

Great, thank you.

If you don't mind, could I take one step further? Similar to my original question,

DATASET:

Dim_01	YYYYMM	ID	Measure_01	Measure_02
AA	201303	1	1	29
AA	201303	2	92	88
AA	201305	1	76	37
AA	201305	2	2	73
AA	201307	1	10	1
AA	201307	2	98	7
BB	201303	3	58	40
BB	201303	4	91	50
BB	201305	3	25	10
BB	201305	4	45	83
BB	201307	3	10	43
BB	201307	4	74	5
CC	201304	1	40	47
CC	201304	2	64	46
CC	201306	1	39	9
CC	201306	2	62	11
CC	201308	1	26	13
CC	201308	2	76	6
DD	201304	3	51	96
DD	201306	3	1	98
EE	201304	4	4	20
EE	201306	4	1	69

DESIRED RESULT:

Dim_01	Value 1	Value 2	# of IDs	# of IDs: Value 1 > Value 2	# of IDs: Value 1 <= Value 2
AA	279	235	2	2	0
BB	303	231	2	1	1
CC	307	132	2	2	0
DD	52	194	1	0	1
EE	5	89	1	0	1

Re: Pivot Table and Set Analysis?

Kushal_Chawda — Fri, 02 Aug 2013 07:15:53 GMT

see the attched one

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 07:19:32 GMT

This is great, thank you. Really appreciate the time you took to help me out here.

Re: Pivot Table and Set Analysis?

Fri, 02 Aug 2013 07:49:44 GMT

Hi Kush,

last question (I promise). What if I would like to add values associated to those IDs, what sort of expression I need to use. Again, thank you for your help.

Below is my desired results.

Dim_01	Grand Total	Measure 01	Measure 02	# IDs >	Value (# IDs >)	Value (# IDs <=)
	946	946	881	3
AA	279	279	235	2	279	0
BB	303	303	231	1	210	93
CC	307	307	132	2	307	0
DD	52	52	194	0	0	194
EE	5	5	89	0	0	89

Re: Pivot Table and Set Analysis?

Kushal_Chawda — Fri, 02 Aug 2013 09:22:02 GMT

i am not sure what you are looking for but see the attched one..

is it what you are looking for

Re: Pivot Table and Set Analysis?

Sat, 03 Aug 2013 09:28:35 GMT

Yes, thank you so much for your help.