Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
d_petrov
Contributor III
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

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

7 Replies
pradosh_thakur
Master II
Master II

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

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
Master II
Master II

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
Contributor III
Contributor III
Author

Again is zero 😞
Any other ideas ?
sunny_talwar

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
Master II
Master II

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

Learning never stops.
d_petrov
Contributor III
Contributor III
Author

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