Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

d_petrov
New Contributor III

Sum values in AGGR for all dates excluding Sun and Sat

I have a list of dates and these should be summarized and grouped by another fields in AGGR.

I tested in a listbox with showing only the dates by using =NOT MATCH(NUM(WEEKDAY(Full_Date)),0,6) and I receive all days except Sun and Sat as expected.

How to get these values inside the AGGR?

I tried something like that:

=IF(NOT MATCH(NUM(WEEKDAY(Full_Date)),0,6),     SUM(AGGR(AVG(Value_1),   Full_Date,    Value_2)))

but it does not work.

How is correct to use only days from Mon to Fri in the AGGR?

Full_Date is selected by the user and can vary between 1 day to months and years.

 

Best Regard,
Drago

Tags (1)
1 Solution

Accepted Solutions

Re: Sum values in AGGR for all dates excluding Sun and Sat

How about this

Sum(Aggr(
Avg({<Full_Date = {"=Match(WeekDay(Full_Date), 0, 6) = 0"}>} Value_1)
, Full_Date, Value_2))

or

Sum(Aggr(
   Avg({<Full_Date = {"=NOT Match(WeekDay(Full_Date), 0, 6)"}>} Value_1)
, Full_Date, Value_2))
7 Replies
pradosh_thakur
Honored Contributor II

Re: Sum values in AGGR for all dates excluding Sun and Sat

SUM(AGGR(AVG({<Full_Date={"=$(=match(num(weekday(Full_Date)),0,6))<1"}>}Value_1),   Full_Date,    Value_2)))
Learning never stops.
d_petrov
New Contributor III

Re: Sum values in AGGR for all dates excluding Sun and Sat

Thanks, I tried it but returns zero.
If I remove the condition and leave only this:
=SUM(AGGR(AVG(Value_1), Full_Date, Value_2))
I have result.
pradosh_thakur
Honored Contributor II

Re: Sum values in AGGR for all dates excluding Sun and Sat

wat about 

SUM(total AGGR(AVG({<Full_Date={"=$(=match(num(weekday(Full_Date)),0,6))<1"}>}Value_1),   Full_Date,    Value_2)))
Learning never stops.
d_petrov
New Contributor III

Re: Sum values in AGGR for all dates excluding Sun and Sat

Again is zero 😞
Any other ideas ?

Re: Sum values in AGGR for all dates excluding Sun and Sat

How about this

Sum(Aggr(
Avg({<Full_Date = {"=Match(WeekDay(Full_Date), 0, 6) = 0"}>} Value_1)
, Full_Date, Value_2))

or

Sum(Aggr(
   Avg({<Full_Date = {"=NOT Match(WeekDay(Full_Date), 0, 6)"}>} Value_1)
, Full_Date, Value_2))
pradosh_thakur
Honored Contributor II

Re: Sum values in AGGR for all dates excluding Sun and Sat

@d_petrov  I think i missed your comment. Please try as sunny suggested.

Learning never stops.
d_petrov
New Contributor III

Re: Sum values in AGGR for all dates excluding Sun and Sat

Thank you,

I just added the NUM() in the expression , because we need it as a number and it worked like that:
SUM(AGGR(AVG({<Full_Date = {"=MATCH(NUM(WEEKDAY(Full_Date)), 0, 6) = 0"}>} Value_1), Full_Date, Value_2))

 

Thanks and regards,

Drago