
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
How to calculate number of days including excluded days
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.
Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
