Set Analysis Challenge

[#1]

Hi Folks

I have a set analysis challenge that I can't figure out.

I have the following expression in a table:

Sum( _rDist ) - ( Sum ( _pDist ) * ( IF ( _rDist <> 0 , Sum ( _rCost ) / Sum ( _pCost ) ) )

Basically what this does is to calculate the difference between a distribution value at a reporting currency (US Dollars) and the distribution value at it's transaction currency (could be any currency) by taking the average fx rate over the period ( _rCost / _pCost ). The IF statement that is embedded ensures that the average fx rate is calculated using only those transactions that have a distribution value.

The above works fine, but what I now need to do is to calculate this value for a prior period (date held in a variable) in the same chart using Set Analysis. For example, the first part of the expression would be this:

Sum ( { $ < DT_ReportDate={"$(vCompDate)"} > } _rDist )

The above works fine, but I am struggling to come up with the correct syntax that would deal with embedding the equivalent IF statement as per the top formula, into the set analysis formula.

Any help would be much appreciated as I'm rapidly losing the will to live!!

[johnw]

Parts of your expression appear inconsistent to me. Sum(_rDist) suggests that you can have more than one value of _rDist for the chart dimension values. IF(_rDist<>0,...) suggests that you will NEVER have more than one value of _rDist for the chart dimension values? Which is it?

Assuming you can have multiple values, wouldn't you want the if inside of the sums?

sum(_rDist)-sum(_pDist)*sum(if(_rDist<>0,_rCost))
/sum(if(_rDist<>0,_pCost))

If so, it's a little complicated, but would this work?

sum({<DT_ReportDate={"$(vCompDate)"} >}_rDist)
-sum({<DT_ReportDate={"$(vCompDate)"} >}_pDist)
*sum({<DT_ReportDate={"$(vCompDate)"},_rDist-={0}>}_rCost)
/sum({<DT_ReportDate={"$(vCompDate)"},_rDist-={0}>}_pCost)

And if you can only have one value for _rDist, guaranteed, couldn't you say so explicitly like this?

only(_rDist)-sum(_pDist)*if(only(_rDist)<>0,sum(_rCost)/sum(_pCost))

If so, again a litle complicated, but would this work?

only({<DT_ReportDate={"$(vCompDate)"}>}_rDist)
- sum({<DT_ReportDate={"$(vCompDate)"}>}_pDist)
* if(only({<DT_ReportDate={"$(vCompDate)"}>}_rDist)<>0,
sum({<DT_ReportDate={"$(vCompDate)"}>}_rCost)
/ sum({<DT_ReportDate={"$(vCompDate)"}>}_pCost))

[#3]

Hi John

Thanks very much for the response, actually your response (particularly about the inconsistency) made me sit and think about the expression I had written and I came to the conclusion that it was pretty much a load of garbage. As there is no chance whatsoever that an _rCost amount will exist on the same row as an _rDist or _rFMV amount then the expression just wouldn't work for me.

The actual expression is much more simple, in that there should not be any IF statements anywhere!!

Your examples are much appreciated anyway, even though on this occasion I'm not going to have to use them.

By the way, I've never used Only( in an expression, what is the basis for using this in an expression?

Thanks for your help,

[johnw]

Well, only() returns the value if there is one and only one matching value for the given dimensions, else it returns null. Sometimes used when you want to be certain that you won't get anything returned where there are multiple matches. Conveniently, it is also an aggregation expression, allowing you to use set analysis in some cases where you otherwise wouldn't be able to.

Mind you, I can't remember if I've ever used it in practice.