Sum multiple Sum-Set analysis statements

[Anonymous]

Hello,

I think I'm misunderstanding the capabilities of Set Analysis.

I'm using this expression:

(sum({$<[Resource Type] = {'Retained'},[#Hours Billed]={0},[#Hours Worked]-={0}>} [#Hours Worked] * #Rate)

+sum({$<[Resource Type] = {'Retained'},[#Hours Worked]={0},[#Hours Billed]-={0}>} [#Hours Billed] * #Rate)

+sum({$<[Resource Type] = {'Retained'},[#Hours Worked]-={0},[#Hours Billed]-={0}>} [#Hours Billed] * #Rate))

To aggregate these calculations based on the Set Modifier conditions.  I tried using the above expression to create three different sets (Exclusive of one another) of data - combine them - and then sum the value returned.

1st set where the Hours Billed are 0 and the Hours worked are not 0 - sum(Hours Worked*Rate)

2nd set where the Hours Worked are 0 and the Hours Billed are not 0 - sum(Hours Billed*Rate)

3rd set where the Hours Worked are not 0 and the Hours Billed are not 0 - sum(HoursBilled*Rate) Hours billed takes precedence here

Finally, I wished to sum all the values returned from these three sets. Am I misunderstanding the capabilities of Set Analysis or the underlying logic behind it?

If anyone could give me some direction that would be great!

Thanks,

Channing

[sunny_talwar · Accepted solution]

Is the value truly 0, or is it null? If it is Null, you can try this:

Sum({$<[Resource Type] = {'Retained'}>} Alt([#Hours Billed], [#Hours Worked]) * #Rate)

or may be this if it is 0

Sum({$<[Resource Type] = {'Retained'}>} If([#Hours Billed] = 0, [#Hours Worked], [#Hours Billed]) * #Rate)