10 Replies Latest reply: Mar 27, 2018 8:03 AM by Elizabeth Viso

# 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.

Thank you!

• ###### 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)?

• ###### 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.

• ###### 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?

Data:

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

];

• ###### Re: Issue Counting Data

Hey Jerry,

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

THANK YOU!

• ###### 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))

• ###### 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?!

• ###### 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.

• ###### Re: Issue Counting Data

Thank you Jerry!

• ###### 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

• ###### 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