Set analysis help

[#1]

Hi guys,

I have a littel problem I need help with Maybe somebody can show me the way.

I receive data over peoples count in a specific room every 30 min periods. 3-5 rooms belong to a specific building. 10-15 Buildings belong to a specific area.

If I wanted to get the average count of people in a specific room over one day. How can I get it if I select a room, a building an area?

If I select a room and a specific day than I would want to get the average peoples count in this room on that day.

If I select multiple rooms and a specific day than I would want to get the average count of people in this rooms on that day summed up.

If I select a building than I want the same result as selecting multiple rooms.

If I select an area I would want the buildings in that area and the corresponding rooms to be summed up.

             -> Building1   ->   Room1, Room2, Room3

Area1    -> Building2    ->  Room1, Room2, Room3, Room4

              > Building3   ->   Room1

SampleTable:

LOAD *

INLINE [

    DateTime, Area, Building, RoomNumber, PeopleCount

    01.02.2012 12:00, Area1, Building1, Room1, 47

    01.02.2012 12:00, Area1, Building1, Room2, 15

    01.02.2012 12:00, Area1, Building1, Room3, 98

    01.02.2012 12:00, Area1, Building2, Room1, 65

    01.02.2012 12:00, Area1, Building2, Room2, 17

    01.02.2012 12:00, Area1, Building2, Room3, 122

    01.02.2012 12:00, Area1, Building2, Room4, 87

    01.02.2012 12:00, Area2, Building1, Room1, 11

    01.02.2012 12:00, Area2, Building1, Room2, 47

    01.02.2012 12:00, Area2, Building1, Room3, 15

    01.02.2012 12:00, Area2, Building1, Room4, 98

    01.02.2012 12:00, Area2, Building2, Room1, 65

    01.02.2012 12:00, Area2, Building2, Room2, 17

    01.02.2012 12:00, Area2, Building2, Room3, 122

    01.02.2012 12:00, Area2, Building2, Room4, 87

    01.02.2012 12:00, Area2, Building2, Room5, 11

    01.02.2012 12:30, Area1, Building1, Room1, 47

    01.02.2012 12:30, Area1, Building1, Room2, 15

    01.02.2012 12:30, Area1, Building1, Room3, 98

    01.02.2012 12:30, Area1, Building2, Room1, 65

    01.02.2012 12:30, Area1, Building2, Room2, 17

    01.02.2012 12:30, Area1, Building2, Room3, 122

    01.02.2012 12:30, Area1, Building2, Room4, 87

    01.02.2012 12:30, Area2, Building1, Room1, 11

    01.02.2012 12:30, Area2, Building1, Room2, 47

    01.02.2012 12:30, Area2, Building1, Room3, 15

    01.02.2012 12:30, Area2, Building1, Room4, 98

    01.02.2012 12:30, Area2, Building2, Room1, 65

    01.02.2012 12:30, Area2, Building2, Room2, 17

    01.02.2012 12:30, Area2, Building2, Room3, 122

    01.02.2012 12:30, Area2, Building2, Room4, 87

    01.02.2012 12:30, Area2, Building2, Room5, 11

];

Right now I have the following expression in my chart

Peoples count, Definition: sum(Peoples Count)

The Problem is when I choose a building it sums up all the people in a 24 hour period in that room. But I want the average people count in those rooms to be summed up. How do I do this?

Thanks.

[jagan · Accepted solution]

Hi,

What do you mean by Average People Count? If it is peoples count in a room and number of 30 min periods then use this expression

=sum(PeoplesCount)/Count(DISTINCT DateTime)

Hope this helps you.

Regards,

Jagan.

[deepakk]

Hi,

try sum(Peoples Count) /count(Date). I am not sure. just give it a try.

Deepak

[CELAMBARASAN]

Hi,

     Try with this

     =Sum(PeoplesCount)/Count(Distinct Floor(DateTime))

Celambarasan

[#5]

Can´t belive the solution was so simple. I was looking at it in a totally different way.

Thanks for this.