Set Analysis and AGGR

[cbaqir]

Any idea why this works just fine: =Sum({$<TASK_NAME={'PTO'}>} ACT_HRS)/sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR))

But this doesn't?=Sum({$<TASK_NAME-={'PTO'}>}ACT_HRS)/sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR))

The only difference is the inclusion/exclusion of the PTO task (= vs -=)

[Nicole-Smith]

QV does show a syntax error (it's a bug), but it should still work.

You could also write it like this (with a - after the $):

Sum({$-<TASK_NAME={'PTO'}>} ACT_HRS)

[cbaqir]

Thanks, Nicole. You are my savior today. The calcs aren't displaying correctly in my graph when I use the aggr statement with the exclusion. I must have a missing ( or something.

[Nicole-Smith]

I don't see anything missing in your expressions.  Unfortunately, without seeing the data, it's hard to determine what's going wrong.

[rwunderlich]

Are you trying to remove PTO from the existing TASK_NAME selections or are you trying for NOT PTO?

-Rob

[cbaqir]

I need to show Actual PTO hours separately from Actual hours so my set analysis for Actual hours should exclude hours where the TASK_NAME = PTO and the Actual PTO hours should only reflect hours in which TASK_NAME = PTO. Does that make sense?

[rwunderlich]

So if you have TASK_NAMEs of

A

B

PTO

And the user selects "B", either directly or indirectly, the -PTO expression should aggregate B only, or A & B?

Could you expand on "isn't working"? Nulls, zeros, results too large or too small?

-Rob

[cbaqir]

The divisor is used to calculate full time equivalent employees and should be the number of working hours in that time period (if there are 160 hours in 1 month and the time frame is one month, the divisor should be 160. If there are 160 in Jan and Feb, the divisor should be 320)

Please see attached.

For Non-PTO hours, this works: =Sum({$<TASK_NAME-={$(=PTO)}>}ACT_HRS)

However, when I add the divisor, it returns a null result: =Sum({$<TASK_NAME-={$(=PTO)}>}ACT_HRS)/sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR)) 

Same thing for the other 2 expressions.

[settu_periasamy]

Hi,

Do you need the Flag in Dimension?

Then, may be try to add the Flag field in Aggregation for Actual PTO  like

=Sum({$<TASK_NAME={'PTO'}>} ACT_HRS)/sum( aggr(max(MONTH_CAP),FISCAL_MONTH_YEAR,Flag))

the result should be look like below

[Nicole-Smith]

Where are you trying to add the expression?  When I put it in a text box, it returns a result.