Set analysis help

Thu, 01 Mar 2012 12:20:30 GMT

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.

Set analysis help

jagan — Thu, 01 Mar 2012 12:35:59 GMT

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.

Set analysis help

deepakk — Thu, 01 Mar 2012 12:37:50 GMT

Hi,

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

Deepak

Re: Set analysis help

CELAMBARASAN — Thu, 01 Mar 2012 12:47:41 GMT

Hi,

Try with this

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

Celambarasan

Set analysis help

Thu, 01 Mar 2012 15:17:52 GMT

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

Thanks for this.

topic Set analysis help in QlikView

Set analysis help

Set analysis help

Set analysis help

Re: Set analysis help

Set analysis help