Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi there, Im new to qlikview and Im working to calculate the average sales per week. Let's say I have the following data
ITEM | WEEK 1 | WEEK 2 | |
---|---|---|---|
10001 | 100 | 0 |
Im trying to find the average sales per week so Im using count(distinct week). But it looks it only counts week 1 rather than two weeks as there is no sale in week 2.
Any suggestions?
Alright I cant figure this out so Im posting our current QV app. Im trying to calculate average sales per week per rooftop. As you can see in the top row the total rooftops is 799, but when involved in the calculation it becomes the number of rooftops that sells certain items. The same happened to weeks. My expression used was COUNT(DISTINCT ROOFTOPS) and my formulate to get the average is SUM(SALES)/COUNT(DISTINCT ROOFTOPS)/COUNT(DISTINCT WEEK). Ideally ROOFTOPS is always the total number (799) and the number of weeks should always be 20.
May be try this
SUM(SALES)/COUNT(TOTAL DISTINCT ROOFTOPS)/COUNT(TOTAL DISTINCT WEEK)
Would you be able to share few rows from your raw data?
Unfortunately my company does not allow that. Actually the logic is like: so far we have 20 fiscal weeks and sales only incurs in 19 out of the 20. When I tried to calculate for average sales per week the count function captures 19 weeks rather than 20 weeks, because the system only captures 19 weeks. How could I enforce in qlikview so the chart captures 20 weeks regardless sales or not?
What is the exact expression you are using? Count(Week) or Count(OfAnotherField)? I won't know what you are doing unless you share more information. See here:
Preparing examples for Upload - Reduction and Data Scrambling
I would suggest you to first doing Cross Table then manipulate the same
Cross (Week, Data)
Load ITEM
from Table;
Then you can use like below
Count({<Week = {'*'}>}Week)
Note - This consider along with nulls, I assume this is you are expecting. If my direction is wrong please explain little more as Sunny mentioned the links
What do you get when you use below expression -
The sample data from you as -
Load * inline [
ITEM, WEEK, Sales
10001, WEEK1, 100
10001, WEEK2,0 };
Share more info to help further as suggested by Experts.
Hello, I have some more info added. Could you please take a look when you get a moment?
Hello, I have some more info added. Could you please take a look when you get a moment?
May be try this
SUM(SALES)/COUNT(TOTAL DISTINCT ROOFTOPS)/COUNT(TOTAL DISTINCT WEEK)
It works! Thank you!!!