topic Re: Aggr Function with Set Analysis for Average Calculation in App Development

Aggr Function with Set Analysis for Average Calculation

christms — Mon, 20 Dec 2021 21:08:46 GMT

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.

Re: Aggr Function with Set Analysis for Average Calculation

lironbaram — Fri, 24 Apr 2020 07:59:21 GMT

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

(Daily% + Daily%)/2

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

Re: Aggr Function with Set Analysis for Average Calculation

christms — Mon, 27 Apr 2020 07:57:22 GMT

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

)

Re: Aggr Function with Set Analysis for Average Calculation

lironbaram — Tue, 28 Apr 2020 11:25:24 GMT

can you post a sample data ?

it'll make it easier to help you ,

it should be 3-4 rows no more