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)

[Kushal_Chawda]

see the attached one.

[Kushal_Chawda]

see the attached one

[#4]

Hi Kush,

thank you for your help and response.

I didn't state the requirement quite clearly (my apology). The calculated dimension needs to be based on sum(M1) vs sum(M2) and time period.

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

As an example, if ID=3 is selected, I would like to display below. Sorry for the confusion and I do appreciate the help. Thank you,.

Dim_01	M1 vs M2	Sum
BB	GT (sum(M1) > sum(M2))	0
BB	LE (sum(M1) <= sum(M2))	98
DD	LE (sum(M1) <= sum(M2))	63

[kaushiknsolanki]

Hi,

     Try this as expression

     sum(aggr(if(sum(M1)>Sum(M2),Sum(M3),Sum(M4)),Dim_01))

     and dimension as Dim_01

Regards,

Kaushik Solanki

[#6]

Hi Kaushik,

thank you for the suggestion. However, I would like to have 2 dimensions in my desired table/report.

1. Dim_01
2. Versus (i.e. M1 vs M2)

The Versus dimension will allow me and users to see calculation in different buckets (there will alo be more metrics in the report). Unfortunately, this is one of the requirements and I am not so sure how to calculate my metrics when Versus dimension is not fixed (hope this makes sense).

[kaushiknsolanki]

Hi,

     You need to come up with the business logic to derive the Versus field.

Regards,

Kaushik Solanki    

[#8]

Hi,

the logic is if the sum of M1 is greater than sum of M2, then ">", else "<=".

The sum of M1 and M2 are calculated based on current selection, and I am having trouble create a flag or a calculated dimension which can indicate whether or not sum of M1 is greater M2.

[Kushal_Chawda]

See the attched one

[kaushiknsolanki]

Dear

     I am not talking about that logic.

     I am talking about how to define the M1 vs M2 as dimension.

     I am sorry but i am not clear.

Regards,

Kaushik Solanki