Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results 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.

1 Solution

Accepted Solutions
MVP

May be try this

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

9 Replies
MVP

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?

MVP

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)

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

What do you get when you use below expression -

The sample data from you as -

ITEM, WEEK, Sales

10001, WEEK1, 100

10001, WEEK2,0 };

Anonymous
Not applicable
Author

Anonymous
Not applicable
Author

MVP

May be try this

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

Anonymous
Not applicable
Author

It works! Thank you!!!

Community Browser