How to calculate number of days including excluded days

[#1]

I have sales data for two years. I have to do analytics on average sales based on cyclic calendar group which consists of all possible calendar dimension - year, quarter, month, year-month, decade, day of week and so on.

So my formula of average sales should look like

SUM(sales) / COUNT(TOTAL calendar.date). E.x. If my cyclic group is turned to "Decade" (10 days not years), formula should be SUM(sales) / 10 for each decade.

Unfortunately, I have dates, where there were no sales. And my formula counts SUM(sales) / 6. Or if my cyclic group is turned to "Month", formula gives me SUM(sales) / 21.

The concept of days count where counting is based on subtraction of MIN MAX dates isn't doing any good because I can choose April in 2012 and 2013 years. So MIN MAX day count starts at 2012 04 01 and ends at 2013 04 31 = 395 days. And what I want is 60. Or if I choose decade, day count in one decade in two years should be 20.

[#2]

Hi,

In order to count the exact number of days, you should create all possible dates.

Take a look at this document  http://community.qlik.com/docs/DOC-3786

View the example "Master Calendar using Autogenerate" at page 5.

I attach also a small example of this metodology.

[#3]

I have all calendar dates. It's just the product hasn't been sold 9 days in April 2012. So I get the count of days of April = 21.

[arthur_dom]

you can add in calendar a field like that:

num(monthend(DATEID) - monthstart(DATEID)  ) as NoOfDays,

and on the graph use this field on the place of count on somethin like

aggr( sum(distinct NoOfDays) ,YearMonth)

[#5 · Accepted solution]

you can try to disconnect dates from products using set analysis like:

COUNT({<product=>}DISTINCT TOTAL salesdate).

or

sum({<product=>} TOTAL fl_count)

I attach an modified example.

[#6]

Thanks a lot, Alessandro Neri . It worked!

I tweaked it a little bit with the answer from kennethg in this thread http://community.qlik.com/thread/14794#93672 because I have a lot of dimensions to ignore.

Thank you once again for everyone.