Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Not applicable

How to calculate the average?

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.

1 Solution

Accepted Solutions
oknotsen
Honored Contributor III

Re: How to calculate the average?

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.

May you live in interesting times!
5 Replies
oknotsen
Honored Contributor III

Re: How to calculate the average?

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.

May you live in interesting times!
Not applicable

Re: How to calculate the average?

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

Re: How to calculate the average?

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.

May you live in interesting times!
Not applicable

Re: How to calculate the average?

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

Re: How to calculate the average?

Take your time and enjoy .

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

May you live in interesting times!