Aggr Function with Set Analysis for Average Calculation

[christms]

Objective: Want to calculate the average % by each department level from the listed/selected dates as shown in the table below, for this example: 1-Mar & 2-Mar. (It must also consider Department D does not have values for staff_att on 1-Mar(Sun) but still populate the average % between the two dates)

Issue: Column in table below-Average between selected days. It seems that my current aggr formula does not consider the first date(1-mar). The other column is working fine.

Formula in Qlik Sense Expression

Average between selected days	staff_att	total_staff	Daily%
avg( aggr( COUNT(DISTINCT {<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [staff_att]) / COUNT(DISTINCT{<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [total_staff]) ,[Division]) )	COUNT(DISTINCT {<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [staff_att])	COUNT({<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} total_staff)	COUNT(DISTINCT {<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [staff_att]) / COUNT(DISTINCT{<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [total_staff])

Any advise will be grateful. Tried TOTAL,SUM, AGGR function but still no chance of getting it right. 

[lironbaram]

hi 

first question is how do you want to calculate the avg . 

(Daily% + Daily%)/2

or 

total staff att / total staff 

if you want the first one 

you need to add the Data dimension to your aggr function , 

something like that 

avg(

aggr(

COUNT(DISTINCT {<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [staff_att])

/

COUNT(DISTINCT{<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [total_staff])

,Date,[Division])

)

[christms]

Hi Lironbaram,

Tried that as well, doesn't work either.

avg(

aggr(

COUNT(DISTINCT {<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [staff_att])

/

COUNT(DISTINCT{<[DATE_FORMAT_DDMMYYYY]=, [CALENDAR_DATE]={"<=$(=DATE(Max([CALENDAR_DATE]),'YYYY-MM-DD'))"}>} [total_staff])

,[DATE_FORMAT_DDMMYYYY], [Division])

)

[lironbaram]

hi 

can you post a sample data ?

it'll make it easier to help you , 

it should be 3-4 rows no more