How to calculate the average?

[#1]

On Day 1, I have 10 employees entering a facility with unique cards. On Day 2, another 16 employees enter the facility. I need to calculate the average presence in the facility in QlikSense. Even though they might swipe the cards more than 1 time, but my answer should be distinct. For this I have used the below expression.

(Count(distinct [Card Number]))

However, I am not able to find the average of the card numbers. In case I choose Day 1, I should get the answer as 10 and if i choose Day 1 & Day 2 in the chart, I should get the answer as (10+16)/2.

Please help.

[oknotsen · Accepted solution]

Try this:

avg(AGGR(count(distinct [Card Number]), dateField))

If you are using it inside a chart, also add the other dimension(s) used after "dateField", separated by commas.

[#3]

Thanks a lot Onno. This works perfectly

Can you please tell me how do I calculate the average without considering weekends (Sat & Sun)? I have a column (Weekday) which shows the weekdays (Sun, Mon, Tue, Wed, Thur, Fri & Sat)  for each of the dates.

Thanks once again for your quick help and support 🙂

[oknotsen]

This depends a bit on the names of your days, so you might have to change the names to the exact names your system is using:

avg({ < Weekday = {'Mon', 'Tue', 'Wed', 'Thur', 'Fri'} > } AGGR(count(distinct [Card Number]), dateField))

Note that the above way ignores future filters on specific weekdays (as fixates the weekdays used). If you want to keep that part flexible, you can also do it the other way around (exclude some days but leave the rest to selection) you could try this:

avg({ < Weekday -= {'Sun', 'Sat'} > } AGGR(count(distinct [Card Number]), dateField))

So Weekday followed by "minues equal". Check if the day names used are 100% the same as those used in your system, case sensitive.

[#5]

Thanks a ton Onno. You saved my day and the frustrating hours I spent in solving this. I am sure I will learn it soon as I am a day old in Qliksense

[oknotsen]

Take your time and enjoy .

If you want to know more about those weird { < { } > }  thingies I used for your answer, search for "Set Analysis".