Calculated Dimension

[#1]

Hi everyone,

I am stuck, and hoping that someone can point me to a right direction here.

I would like to create a dimension which compares 2 columns (M1 vs M2) whose members are GT and LE. If M1 is greater than M2, then sum(M3) on GT, else sum(M4) on LE.

My problem is that my underlying dataset contains YearMonth, but the desired dimension and calculation depend on (entired) current selection, and creating a calcuated dimension in the load script will not get what I need.

Any help or suggestion will be much appreciated. Thank you.

DATASET:

(see attached)

Desired Result:

Dim_01	M1 vs M2	Sum
AA	GT (M1 >M2)	sum(M3)
AA	LE (M1<=M2)	sum(M4)
BB	GT (M1 >M2)	sum(M3)
BB	LE (M1<=M2)	sum(M4)
CC	GT (M1 >M2)	sum(M3)
CC	LE (M1<=M2)	sum(M4)
DD	GT (M1 >M2)	sum(M3)
DD	LE (M1<=M2)	sum(M4)
EE	GT (M1 >M2)	sum(M3)
EE	LE (M1<=M2)	sum(M4)

[#2]

Hi Kaushik,

I could be missing something here, but defining M1 vs M2 as dimension is what I am having trouble with.

[#3]

Hi Kush,

thank you for your qvw file. It still does not provide what I am looking for though. It seems you create M1 vs M2 dimension in the load script by summing up values by Dim_01 and ID.

But when I select YYYYMM=201303 in the report, you will see that sum(M1) = 93 < 117 = sum(M2) on Dim_01=AA, however, your M1 vs M2 dimension is still M1 > M2.

[Kushal_Chawda · Accepted solution]

see the attached one