5 Replies Latest reply: Sep 8, 2017 5:09 AM by Sunny Talwar RSS

    Create a histogram with a calculated value

    Mattias Brandt

      Hi guys,

       

      I'm new to Qlikview and I am trying to create an histogram för the number of occurences of a calculated value.

       

      To clarify my problem, I have the following columns:

       

      ActivityNumberMeeting_bookedMeeting_CancelledDays_till_cancelled
      1251062017-01-152017-05-15120
      1478412017-02-012017-02-054
      ............


      I have calculated the diffrence between the two dates in the new column Days_till_cancelled by using:

      max({$Activity={'Booked'}> Date}-max({$Activity={'Cancelled'}> Date} = Difference in days

      Now I would like to create an histogram based on this new column to see if there is any signs that meetings that are booked far ahead gets more frequently cancelled than meetings that are booked sooner.


      I added the calculation as a variable and tried creating the table with Round(Value,Binwidth), as described by hic in his blogpost Recipe for a Histogram. However, this does not work when the value is a calculated variable.


      Any ideas how I can create this?

       


      Thanks in advance!


      /Mattias


       

        • Re: Create a histogram with a calculated value
          Sunny Talwar

          Would you be able to share a sample where you are trying to do this? How exactly have you done this?

            • Re: Create a histogram with a calculated value
              Mattias Brandt

              Hi stalwar1,

               

              Unfurtunaly not, there is more data that calculates the dates for each activity.

               

              But for the sake of of, this is a sample table:

               

              ActivityNumberMeetingCancelledMeeting_Booked
              8806782017-12-312016-10-21
              2453612017-11-212017-08-10
              2643682017-10-232017-02-03
              5377482017-10-172017-08-22
              4282332017-10-162017-08-22
              1605812017-10-122017-08-25
              3932212017-10-112017-08-24
              8433392017-10-092017-04-04
              2340932017-10-092016-11-16
              3127292017-10-092017-04-20
              7570622017-10-062017-08-25
              3502012017-10-05

              2017-04-05

               

              To calculate the days between the dates, I used: Meeting_Booked-MeetingCancelled.

              So for the first row, I get 436 days, secon 327 days etc. So now the table is like this:

               

                

              ActivityNumberMeetingCancelledMeeting_BookedDays_till_Cancelled
              8806782017-12-312016-10-21-436
              2453612017-11-212017-08-10-103
              2643682017-10-232017-02-03-262
              5377482017-10-172017-08-22-56
              4282332017-10-162017-08-22-55
              1605812017-10-122017-08-25-48
              3932212017-10-112017-08-24-48
              8433392017-10-092017-04-04-188
              2340932017-10-092016-11-16-327
              3127292017-10-092017-04-20-172
              7570622017-10-062017-08-25-42
              3502012017-10-052017-04-05-183

               

              So now I want to make a histogram to see the distrubution of days a meeting get cancelled, and I have no clue how to do this with a calculated value.

                • Re: Create a histogram with a calculated value
                  Sunny Talwar

                  Are you looking for something like this?

                   

                  Capture.PNG

                   

                  Dimension

                  =Aggr(If(MeetingCancelled - Meeting_Booked < 100, Dual('<100 days', 1),

                  If(MeetingCancelled - Meeting_Booked < 200, Dual('>=100<200 days', 2),

                  If(MeetingCancelled - Meeting_Booked < 300, Dual('>=200<300 days', 3),

                  If(MeetingCancelled - Meeting_Booked < 400, Dual('>=300<400 days', 4),

                  If(MeetingCancelled - Meeting_Booked < 500, Dual('>=400<500 days', 5)))))), ActivityNumber)

                   

                  Expression

                  =Count(DISTINCT ActivityNumber)

                    • Re: Create a histogram with a calculated value
                      Mattias Brandt

                      YES!

                       

                      Exactly what I needed, thank you!

                       

                      A follow up question, I want to ommit all where the Days_till_cancelled is null, since the meeting is not cancelled.

                      The majority of meetings does not get cancelled, so it would be nice the not have that in my diagram.

                       

                      Example of the table again but with blank cells in MeetingCancelled

                       

                      ActivityNumberMeetingCancelledMeeting_Booked
                      8806782017-12-312016-10-21
                      2453612017-11-212017-08-10
                      2643682017-10-232017-02-03
                      5377482017-10-172017-08-22
                      4282332017-08-22
                      1605812017-10-122017-08-25
                      3932212017-10-112017-08-24
                      8433392017-10-092017-04-04
                      2340932016-11-16
                      3127292017-10-092017-04-20
                      7570622017-08-25
                      3502012017-10-05

                      2017-04-05