topic Re: Conditional dimension in chart in App Development

Conditional dimension in chart

Anonymous — Wed, 18 May 2016 14:13:00 GMT

Dear community

I'm not sure that what I want is possible. I'd be very interested to hear good suggestions.

Let's say I want to monitor the flow of people between a number of rooms between two different points in time.

Example data:

PeopleID	RoomID	Time
1	A	03:02
1	A	03:03
1	B	03:04
2	B	03:02
2	B	03:03
2	B	03:04

Let's say I'm interested in the following snapshot times: X = 03:02 and Y = 03:04. (I don't know the time of the snapshot that I'm intersted in when data is loaded.)

The chart that I want to produce shoud have two dimensions and one measure like this:

RoomID_at_X	RoomID_at_Y	Count(PeopleID)
A	A	0
A	B	1
B	A	0
B	B	1

(The combinations A,A and B,A don't exist, so they would not be included in the output table. I just included them for illustration.)

The table is a grouping of the different unique combinations of RoomIDs at the two snapshot times.

I'm struggling with defining the dimensions. I have tried "If(Time=X, RoomID)" and "If(Time=Y, RoomID)", but this doesn't give me what I need. (Besides, using "if" in this situation seems to be very slow.)

Thanks for all good ideas!

Tor

Re: Conditional dimension in chart

Thu, 19 May 2016 11:36:04 GMT

Hi Tor,

I think that you should use Time and RoomID as dimensions and Count(PeopleID) as measure.

If the snapshot time (Time field) is too detailed (with minutes and seconds) you can create another filed based on it for example year/month/day/hour (if date is enough just use Date(Time) as calculated dimension).

That will provide you to create table object with rooms and dates and number of people in them.

EDIT: Here you go: I created simple pivot table based on your example data. Please take a look below.

BR,

Milosz

Re: Conditional dimension in chart

Anonymous — Thu, 19 May 2016 12:49:03 GMT

Thanks, Milosz! However, it's not exactly what I need. Let me be more clear:

The chart needs to have two dimensions (each of which represent a point in time) and one measure (the count).

So, if the chart representation is a table, it will have 3 columns: Two columns with the ROOMID at the selected points in time, and one that shows the count.

Yes, I can put this into a pivot table or any other chart type or extension as soon as I have been able to achieve this data structure. But my challenge is to write the chart expressions to achieve this data structure 🙂

Thanks again!

Tor

Re: Conditional dimension in chart

Thu, 19 May 2016 13:22:45 GMT

Hi Tor,

What Count(PeopleID) measure (3rd column) should show in your approach? Count of PeopleID from timestamp X (1st column) or timestamp Y (2nd column) or count/sum/difference from both timestamps (A+B)?

I think that your example shows wrong value because in source table you have PeopleID=1 for room A in time 3:02 and in second table you show Count(PeopleID) for that room and time =0 ?

In my example you can choose 2 points in time and show them for room A, room B - please think about it because it gives you basically what you want. Unless you need difference in people number between two points in time - that will not work both in your approach and mine.

BR,

Milosz

Re: Conditional dimension in chart

Anonymous — Fri, 20 May 2016 14:19:54 GMT

Hey Milosz! Thanks so much for following up!

Let me try to explain:

Milosz:

What Count(PeopleID) measure (3rd column) should show in your approach? Count of PeopleID from timestamp X (1st column) or timestamp Y (2nd column) or count/sum/difference from both timestamps (A+B)?

Answer: Neither of these 🙂 In the Count(PeopleID) column, I want to count the number of people that appears in both timestamp X AND timestamp Y.

So, row 2 in the desired output table refers to row 1 and 3 in the source table.

And row 4 in the desired output table refers to row 4 and 6 in the source table.

You are right that there is an error, though. I should have written "Count (Distinct PeopleID)". The Distinct keyword would ensure that I only count number of unique people in each group of X and Y.

Milosz:

I think that your example shows wrong value because in source table you have PeopleID=1 for room A in time 3:02 and in second table you show Count(PeopleID) for that room and time =0 ?

Answer: It is not wrong 🙂 It's just a difficult case - I think. The first row in the desired output table seeks to count the number of unique people who was present in room A at time 03:02 AND was still present in room A at time 03:04. And that is not true for any of the two people in the example.

If Qlik supported SQL, I would do the following (using 'SourceTable' as my source table):

SELECT s1.roomID as RoomTimeX, s2.roomID as RoomTimeY, count(distinct s1.peopleID) as CountDistinctPeopleID

FROM SourceTable s1

cross join SourceTable s2

where s1.time='03:02' and s2.time='03:04'

group by s1.roomID, s2.roomID

I tested it here:

What do you think? Can this be achieved in Qlik?