How to use set Analysis to solve expression which associate with dimention?

[#1]

Variables:

vContractDate =min(ContractDate)

Qustion just like above pic, I want to calc each CustID's asset that DoneDate >=ContractDate

That means the condition associate with dimention

How to solve this ?

[jonathandienst]

Hi

You can't use a set expression in this case, because set expressions are evaluated on the entire data set, outside the context of the chart and know nothing of the dimensions in the chart. The alternative is sumif.

Something like:

Sum(If(DoneDate>=ContractDate, Asset))

Jonathan

[#3]

Thanks your reply

IF using sumif, it would be related with client selections ,for example I choose doneDate:20100108, the result will be all zero.

It also a big problem.

Any other solution?

[jonathandienst]

Hi

If you want to override user selections, you can combine a set expression with the sumif. The user selection override does not need the chart context, so you can use a set expression for this purpose.

To override the selection of DoneDate use a blank set expression target like:

Sum({<DoneDate=>} If(DoneDate>=ContractDate, Asset))

Override DoneDate and CustID selections:

Sum({<DoneDate=, CustID=>} If(DoneDate>=ContractDate, Asset))

Override all user selections (careful with this one):

Sum({1} If(DoneDate>=ContractDate, Asset))

Jonathan

[#5]

Good done!,Thanks

[#6]

Good done, Thanks

[#7]

Actually you can resolve a set expression at the dimension level with the use of variables and conditional testing.

I recently created a Wiki on the subject which you might find of interest.

Regards,

Gordon