Issue Counting Data

[hammermill21]

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.

Thank you!

[jwjackso]

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]

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]

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

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]

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

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

[stevedark]

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]

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]

Hey Jerry,

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

THANK YOU!

[hammermill21]

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 · Accepted solution]

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.