Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Do Not Ignore Zero In Count

Hi there, Im new to qlikview and Im working to calculate the average sales per week. Let's say I have the following data

ITEMWEEK 1WEEK 2
100011000

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.

Capture.PNG

1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

9 Replies
sunny_talwar

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

Anonymous
Not applicable
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
Digvijay_Singh

What do you get when you use below expression -

Capture.PNG

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
Not applicable
Author

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

Anonymous
Not applicable
Author

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

sunny_talwar

May be try this

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

Anonymous
Not applicable
Author

It works! Thank you!!!