topic Re: Set Analysis and AGGR in QlikView

Set Analysis and AGGR

cbaqir — Wed, 15 Jul 2015 20:44:41 GMT

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 -=)

Re: Set Analysis and AGGR

Nicole-Smith — Wed, 15 Jul 2015 20:47:13 GMT

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)

Re: Set Analysis and AGGR

cbaqir — Wed, 15 Jul 2015 20:49:53 GMT

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.

Re: Set Analysis and AGGR

Nicole-Smith — Wed, 15 Jul 2015 20:55:00 GMT

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

Re: Set Analysis and AGGR

rwunderlich — Wed, 15 Jul 2015 22:33:34 GMT

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

-Rob

Re: Set Analysis and AGGR

cbaqir — Wed, 15 Jul 2015 22:38:46 GMT

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?

Re: Set Analysis and AGGR

rwunderlich — Wed, 15 Jul 2015 23:09:02 GMT

So if you have TASK_NAMEs of

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

Re: Set Analysis and AGGR

cbaqir — Thu, 16 Jul 2015 15:21:09 GMT

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.

Re: Set Analysis and AGGR

settu_periasamy — Thu, 16 Jul 2015 16:49:55 GMT

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

Re: Set Analysis and AGGR

Nicole-Smith — Sun, 19 Jul 2015 01:14:15 GMT

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

Re: Set Analysis and AGGR

rwunderlich — Sun, 19 Jul 2015 17:26:28 GMT

I think the problem is the granularity of the dimensions, although I haven't been able to sort out a solution. The calculated Dim against TRX_Date produces two different aggregations of MONTH-CAP/ I don't see the problem if you use FISCAL_MONTH_YEAR as the Dim.

Re: Set Analysis and AGGR

cbaqir — Wed, 22 Jul 2015 16:44:45 GMT

I am adding the expression within the Expressions tab in the chart. You are right that I also get a value in a text box. I think I was missing the FLAG dimension in the expression,