Pivot Table and Set Analysis?

[#1]

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

[Kushal_Chawda]

See the attched application

[#3]

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?

[Kushal_Chawda]

Hi ,

see the attched one

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

[#5]

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.

[Kushal_Chawda · Accepted solution]

Hi,

Got your point..

see the attached one

[#7]

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.

[#8]

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

[Kushal_Chawda]

see the attched one

[#10]

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