8 Replies Latest reply on Mar 8, 2016 3:14 PM by Mattia Pradella

# Do Not aggregate

Hi All,

I have the below formula giving me the "cannot mix aggregate and non aggregate arguments" on the minus signs. I cannot add a "sum" before the "{include" as it would change all the numbers. I though about having a parameter calculated with a formula but no luck.

Anyone able to help?

if countd( [Fortnight end date])*80 - { include[EMPLOYEENUMBER]: sum([STD Hours PAID])}>0 then countd( [Fortnight end date])*80 -
{ include[EMPLOYEENUMBER]: sum([STD Hours PAID])}

else 0

end

• ###### 1. Re: Do Not aggregate

unfortunately this doesn't work as what I need is the sum of the individual numbers in the "include" statement. Is there any option to do not aggregate the "countd( [Fortnight end date])*80".

thanks.

Mattia

• ###### 2. Re: Do Not aggregate

I think you can simplify the calculation as below??

[Calculation1]

max(0,countd([Fortnight end date])*80 - attr({include[Employeenumber]: sum([STD Hours PAID])}))

Thanks,

Shin

9.0 attached.

1 of 1 people found this helpful
• ###### 3. Re: Do Not aggregate

Hi Shin,

unfortunately it doesn't work. I've tried before with the "Attr" function but as result all the numbers turn to be 0.

I'd like to have the include function not aggregated as it is the sum of multiple individual records (it is a sum of individual gaps).

Thanks.

• ###### 4. Re: Do Not aggregate

Are you able to share small sample of data as packaged workbook?

Thanks,

Shin

• ###### 5. Re: Do Not aggregate

hi Shin, please find it attached.

the formula I'm using right now (not ideal) is B/C PAID Undertime Cycle hours fortnights.

So to do not aggregate the initial calculation I'm using a parameter.

if [Fortnights]*76- { include[EMPLOYEENUMBER]: sum([STD Hours PAID])}>0 then [Fortnights]*76 -
{ include[EMPLOYEENUMBER]: sum([STD Hours PAID])}

else 0

end

Have a look and let me know if you can help.

Thanks for your  help.

Mattia

• ###### 6. Re: Do Not aggregate

Change LOD calc from "include" to "fixed".

and convert all the filters into "Context" to make filter effective under "Fixed".

Looks like working to me ??

[B/C PAID Undertime cycle hours fortnights 1]

max(0,{fixed:max([B/C Number of Dates])}*76

-{fixed[EMPLOYEENUMBER],[DEPOT NAME]: sum([STD Hours PAID])})

[B/C Number of Dates]

{fixed [DEPOT NAME]:countd([Fortnight end date])}

Thanks,

Shin

9.0 attached.

• ###### 7. Re: Do Not aggregate

Sorry, simply below formula works.

[B/C PAID Undertime cycle hours fortnights 1]

if {fixed:max([B/C Number of Dates])}*76- { include[EMPLOYEENUMBER]: sum([STD Hours PAID])}>0 then [Fortnights]*76 -

{ include[EMPLOYEENUMBER]: sum([STD Hours PAID])}

else 0

end

[B/C Number of Dates]

{fixed [DEPOT NAME]:countd([Fortnight end date])}

Thanks,

Shin

1 of 1 people found this helpful
• ###### 8. Re: Do Not aggregate

Hi Shin, thanks for your help!!!!

With your formula I was getting an incorrect number of shifts, In the end I got it working with the below:

if ([Number of Fortnights] )*76- { include[EMPLOYEENUMBER]: sum([STD Hours WORKED])}>0 then [Number of Fortnights]*76 -
{ include[EMPLOYEENUMBER]: sum([STD Hours WORKED])}

else 0

end

Number of fortnights:

{ include[DEPOT NAME]: sum([Fortnight end date])}