Do Not Ignore Zero In Count

[Anonymous]

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.

[sunny_talwar]

Would you be able to share few rows from your raw data?

[Anonymous]

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?

[sunny_talwar]

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

Qlik Community Tip: How to Get Answers to Your Post

[Anil_Babu_Samineni]

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

[Digvijay_Singh]

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.

[Anonymous]

Hello, I have some more info added. Could you please take a look when you get a moment?

[Anonymous]

Hello, I have some more info added. Could you please take a look when you get a moment?

[sunny_talwar · Accepted solution]

May be try this

SUM(SALES)/COUNT(TOTAL DISTINCT ROOFTOPS)/COUNT(TOTAL DISTINCT WEEK)

[Anonymous]

It works! Thank you!!!