count total and filter selections

[Qliksense_77]

Hi,

I want to do an average sales per day for departments. The average should be based on total number of days in the period - also the days without sales.

I get the correct averages with this expression and departments as dimension:

Sum(sales)/count (total distinct date)

I get the date field from a master calendar. Therefore I have all dates, even if there are days without sales. For instans the periode for january to june in 2021 gives me 180 days.

My problem is that the averages changes if a department is selected.  Then the count of days is limited to number of days with sales.

For instans department A has a sum of sales in 1. half 2021 of 1000$. Then the average sale per day should be 5,5$ because there are 180 days. And I get this correct with the expression above. But if department A is selected then the average sale changes to 8$ because there are only 125 with sales in the period.

Anybody know what expression I should use for my charts, so the calculated averages dont change with filter selections?

[AshutoshBhumkar]

Hello,

You can ignore the selections of those dimensions in your expression.

E.g Sum({<Department = , Date = , Category = >}Sales)

So that your average will not be affected on selections of above dimensions in bold.

Thanks,

Ashutosh

[anthonyj · Accepted solution]

Hi @Qliksense_77 ,

You can add a set analysis to your count of dates like this:

Sum(sales)/count (total {$<Department=>}distinct date)

This way your denominator won't change when selecting departments.

Regards

Anthony

[Qliksense_77]

Thanks for the replies. Its only the count of days that I want to lock. Therefore I ended up using the solution from @anthonyj . It works perfect 🙂