How to write Expressin

[wenkew186]

Hi Guys,

I have a pivot table. like attachment.  2 dim and one expression.

Column "Cluster count of Subregion" just for compare. it means how many Custer belong to the Sub region.

Expression Logic is : Sum(Value)/Sum(DayNum)

Requirement point is :

    when "Cluster count of Subregion" >1, the Expression result it should be  every Cluster's Sum(Value)/Sum(DayNm)  add.

    when "Cluster count of Subregion" =1 or 0, the Expression result is Sum(Value)/Sum(DayNum).

The background of the question:

because data source is excel file. and the Subregion data is added by every (Cluster data /DayNum) if have Cluster.

like:

Sum({<Subregion='Subregion1'>}Value) /Sum({<Subregion='Subregion1'>}DayNum)

<>

Sum({<Cluster='Cluster1'>} Value)/Sum({<Cluster='Cluster1'>}DayNum)

+Sum({<Cluster='Cluster2'>} Value)/Sum({<Cluster='Cluster2'>}DayNum)

+Sum({<Cluster='Cluster3'>} Value)/Sum({<Cluster='Cluster3'>}DayNum)

+Sum({<Cluster='Cluster4'>} Value)/Sum({<Cluster='Cluster4'>}DayNum)

......................................

Question:    How to write the Expression ?

Please help ?

thanks,

Kevin.

[wenkew186]

Sorry for my Sample is not clear. I re-uploading the sample.

My Expression like this:

1. Cluster is dynamic, so it should be control by variable. I can't every Subregion and ever Cluster hard code in expression. 

2. the Total is null, how to let it show the Sun of the Row like straight? i have to use pivot table .

I have conceive to use "COUNT(DISTINCT {<Subregion>} Cluster)"  to judgment which Subregion have more than 1 Cluster then Expression use every Cluster SUM(Amount)/sum(DayNum) add, if not, then just SUM(Amount)/sum(DayNum).

How about pick match function? and it may be need loop calculate in Expression, ! I'm not sure.  Can any help me?

thank you so much!

[#3]

Hi

Give me sample data for excel.

while using match function to avoid the nested if conditions .

Thanks,

Srinu

[wenkew186]

Hi,

this sample is right.

[ramoncova06]

have you tried with sum(total <subregion> Amount)/sum( total <subregion> daynum)

[sunny_talwar]

See if this is what you are looking for? PFA

Best,

Sunny

[wenkew186]

thank you sunindia,

yes, I hope look the Total is like this.

But it is not my only problem,

1.I need dynamic the expression. now all the hard code in here. like Subregion='A' and Cluster ='A1'. I need use variable to replace it.

2. another problem in my sample.  when click a Subregion, the Dim Group not drop down and no data.

[wenkew186]

thanks for your response Ramon,

My expression is like this, and because I need add every Cluster Sum(Amount)/Sum(DayNum) when Subregion has Cluster. so that why I use this complex expression and nit use just Sum(Amount)/Sum(DayNum).

thanks.

[sunny_talwar · Accepted solution]

Check this out: (PFA)

Best,

Sunny

[sunny_talwar]

Did you try the the application I posted for you?

Best,

Sunny