4 Replies Latest reply: May 20, 2016 10:19 AM by Tor Kielland RSS

    Conditional dimension in chart

    Tor Kielland

      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:

       

      PeopleIDRoomIDTime
      1A03:02
      1A03:03
      1B03:04
      2B03:02
      2B03:03
      2B03: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_XRoomID_at_YCount(PeopleID)
      AA0
      AB1
      BA0
      BB1

       

      (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
          Miłosz Grabara

          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.

           

          pivot.png

           

          BR,

          Milosz

            • Re: Conditional dimension in chart
              Tor Kielland

              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
              Miłosz Grabara

              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.

               

              pivot2.png

               

              BR,

              Milosz

                • Re: Conditional dimension in chart
                  Tor Kielland

                  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:

                  Bilde1.png

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