Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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.
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.
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.
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)
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.
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.