topic Re: Aggr Sum between Dates in QlikView

Aggr Sum between Dates

billuran — Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Aggr Sum between Dates

Clever_Anjos — Thu, 04 Aug 2016 17:32:29 GMT

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

Re: Aggr Sum between Dates

sunny_talwar — Thu, 04 Aug 2016 17:53:07 GMT

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

Re: Aggr Sum between Dates

Clever_Anjos — Thu, 04 Aug 2016 17:56:00 GMT

Hawk eyes

Re: Aggr Sum between Dates

billuran — Thu, 04 Aug 2016 18:06:23 GMT

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

Re: Aggr Sum between Dates

Clever_Anjos — Thu, 04 Aug 2016 19:24:22 GMT

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)

)