Aggr Sum between Dates

[billuran]

Hi Guys, I know this question as been asked 1000 times, but i have not found this answer yet.

I have a biweekly date (MaxPP), with values "Service Units". So i want to sum the service units by most recent 4 biweekly dates or 56 days less then the max date. here is my expression with out the dates.

=Aggr(sum([Service Units]*Weight),Charged_BU,Charged_deptid)

I have figured out how to do it without the aggr when I just add my hours value in a separate expression.

sum({<[Exclude Hours]={'NO'},PP={">=$(=max({1}MaxPP))-56)<=$(=max({1}MaxPP))"}>} Hours)

So how can i do it with the Aggr above?

Thanks guys!

[Clever_Anjos]

max({1}MaxPP))-56 returns a date?

If yes, please format it using Date(max({1}MaxPP))-56,'MM/DD/YYYY') to match PP format

[sunny_talwar]

Also there seems to be issue with too many parenthesis getting closed:

Sum({<[Exclude Hours] = {'NO'}, PP = {">=$(=Max({1}MaxPP))-56)<=$(=Max({1}MaxPP))"}>} Hours)

Try this:

Sum({<[Exclude Hours] = {'NO'}, PP = {">=$(=Max({1}MaxPP)-56)<=$(=Max({1}MaxPP))"}>} Hours)

Also, I would do as Clever pointed out

[Clever_Anjos]

Hawk eyes

[billuran]

OK but i need help on the Aggr one. I have the bottom working fine. Thanks for the quick reply.

[Clever_Anjos]

What do you want with your AGGR()? This function returns a list, are you aware of this? Are you making another function over AGGR()?

Pitfalls of the Aggr function

Maybe:

SUM({<[Exclude Hours] = {'NO'}, PP = {">=$(=Max({1}MaxPP))-56)<=$(=Max({1}MaxPP))"}>}

Aggr(

     sum(

     {<[Exclude Hours] = {'NO'}, PP = {">=$(=Max({1}MaxPP))-56)<=$(=Max({1}MaxPP))"}>}  [Service Units]*Weight),

Charged_BU,Charged_deptid)

)