Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

hammermill21
Contributor II

Issue Counting Data

Hello,

I have an excel sheet that I'm trying to count the individual Rooms which are 44. But instead, regardless of what I do, it is counting the individual count of the days where you see 13, 14, 15 which is a total 484 (which is 44 rooms x the 11 days that are marked). I'm not sure why this is happening, all I want is a count of the rooms which is 44.

Capture1.PNG

Thank you!

1 Solution

Accepted Solutions
jwjackso
Contributor III

Re: Issue Counting Data

Just using Count will give you all the records, which includes each (room,day).  This gives you too many rooms.  Using Count Distinct gives you the count of only the rooms, it does not distinguish rooms that have the same name but are actually in different facilities.  This gives you too few rooms.  Using Aggr function gives you a distinct count of rooms for each facility.

10 Replies
jwjackso
Contributor III

Re: Issue Counting Data

I'm guessing you used the crosstable in the script so that room OR-1 has 11 entries.  Did you try Count(Distinct Room)?

hammermill21
Contributor II

Re: Issue Counting Data

Hey Jerry,

Yes, I used a crosstable. When I count "distinct" rooms I get 24, this is happening because I have multiple facilities with the same room name. 

jwjackso
Contributor III

Re: Issue Counting Data

I created some dummy data and created a table with facility and count(distinct room).  What does your table/expression look like?

Count.PNG

Data:

Load * Inline [

Facility,Room,Day

UHT,OR-1,1

UHT,OR-1,2

UHT,OR-1,3

UHT,OR-2,2

UHT,OR-2,3

UHT,OR-3,1

UHT,OR-3,3

UHS,OR-1,1

UHS,OR-1,2

UHS,OR-2,1

UHS,OR-2,2

];

jwjackso
Contributor III

Re: Issue Counting Data

Are you doing the count in a text box?  For that, I used this formula:

='Total Rooms='&Sum(Aggr(Count(Distinct Room),Facility))

Count2.PNG

MVP
MVP

Re: Issue Counting Data

Count will count all values, regardless of whether they are zero or not.  What I suspect you want to be doing instead is a sum(Value), where Value is the field of zeros and ones that you crosstable into?

Hope that helps,

Steve

sasiparupudi1
Honored Contributor III

Re: Issue Counting Data

May be just use the expression count (Distinct Room)

Dimensions

Facility,Room,Humidity, Year,Month


Otherwise,pl post your app sample or data you are using


Hth

Sasi

hammermill21
Contributor II

Re: Issue Counting Data

Hey Jerry,

I attached a sample of my data to the original message.

THANK YOU!

hammermill21
Contributor II

Re: Issue Counting Data

If I do this in the text box it does give me the correct count. THANK YOU.

But I'm still wondering why I can't do a regular count?!

jwjackso
Contributor III

Re: Issue Counting Data

Just using Count will give you all the records, which includes each (room,day).  This gives you too many rooms.  Using Count Distinct gives you the count of only the rooms, it does not distinguish rooms that have the same name but are actually in different facilities.  This gives you too few rooms.  Using Aggr function gives you a distinct count of rooms for each facility.

Community Browser