topic Do Not Ignore Zero In Count in QlikView

Do Not Ignore Zero In Count

Anonymous — Tue, 30 May 2017 18:32:50 GMT

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.

Re: Do Not Ignore Zero In Count

sunny_talwar — Tue, 30 May 2017 18:43:46 GMT

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

Re: Do Not Ignore Zero In Count

Anonymous — Tue, 30 May 2017 18:50:35 GMT

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?

Re: Do Not Ignore Zero In Count

sunny_talwar — Wed, 31 May 2017 02:30:53 GMT

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

Re: Do Not Ignore Zero In Count

Anil_Babu_Samineni — Wed, 31 May 2017 02:35:30 GMT

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

Re: Do Not Ignore Zero In Count

Digvijay_Singh — Wed, 31 May 2017 04:06:35 GMT

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.

Re: Do Not Ignore Zero In Count

Anonymous — Wed, 31 May 2017 15:31:13 GMT

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

Re: Do Not Ignore Zero In Count

Anonymous — Wed, 31 May 2017 15:31:25 GMT

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

Re: Do Not Ignore Zero In Count

sunny_talwar — Wed, 31 May 2017 15:36:31 GMT

May be try this

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

Re: Do Not Ignore Zero In Count

Anonymous — Wed, 31 May 2017 15:47:15 GMT

It works! Thank you!!!