9 Replies Latest reply on Mar 3, 2016 1:18 PM by pooja.gandhi

    How to count

    Bin Xiao

      Dear all,

       

      I have a following data set:

       

      As you can see, the first Activity ID has the event type - Stop Activity; the second Activity ID does NOT have the event type - Stop Activity.

       

      What I need to do is:

       

      1. Count the number of Activity ID which has event type - "Stop Activity", Count the number of Activity ID which does NOT have event type - "Stop Activity";

       

      2. For all the Activity ID which does NOT have "Stop Activity", get the last event type. For example, the last event type for the second activity ID is RouteDelivery. Then I want to create a pie chart based on "the last Event Type and the count" similar to the following:

       

       

      Thank you very very much for your help!!! I really appreciate it!!!!

        • 1. Re: How to count
          Bin Xiao

          Anyone knows how to do it? Thank you very very much for your help in advance!!!

          • 2. Re: How to count
            Nirupam Haas Tirukkovalluri

            Create a Calculation

             

            [Last Event Type]

             

            {FIXED [Activity ID] : MAX([Event Type])}

             

            [Is StopActivity ?]

             

            { FIXED [Activity ID], [Sequence ID] : MIN(IF [Event Type]='StopActivity' THEN 'StopActivity' else [Last Event Type] END)}

             

            [StopActivity or Other]

             

            if [Is StopActivity ?] = 'StopActivity' then 'StopActivity' else 'Other' END

             

            find the workbook and sample file, hope this helps

            1 of 1 people found this helpful
            • 3. Re: How to count
              Bin Xiao

              Hi Nirupam,

               

              Thank you very very much for your response!! Let me try it out!! I will let you know very soon!!

              • 4. Re: How to count
                Bin Xiao

                Hi Nirupam,

                 

                Your solution is almost perfect!! Thank you so so much!

                 

                Just one problem. My Event type actually is NOT number (it is a word)... So Max (Event Type) might not work... Max (sequence ID) should work... but I want to see the last event type.

                 

                Do you know how to do that?

                 

                Thank you very very much!!!

                • 5. Re: How to count
                  pooja.gandhi

                  Hey Bin!

                   

                  Something like this might work:

                   

                  Calc 1:

                  IF { fixed [Activity ID] : countd(if [Event Type] = 'Stop' then [Activity ID] end) } = 1 then 'Has StopActivity'

                  else "Doesn't Have StopActivity" end

                   

                  Calc 2:

                  { fixed [Activity ID]: max(IF [Seq ID] = {fixed [Activity ID]: MAX( IF [Calculation1] = "Doesn't Have StopActivity" then [Seq ID] end ) } then [Event Type] end ) }

                   

                  1 of 1 people found this helpful
                  • 6. Re: How to count
                    Bin Xiao

                    Hi Pooja,

                     

                    Thank you so much for your help!!

                     

                    Your solution is quite good! But I also need to see the specific last event type for the transaction which does not have stop activity. For example, for the second activity, I need to see the last event type - Route Delivery.

                     

                    Thank you very very much for your help in advance!!!

                     

                    best,

                    • 7. Re: How to count
                      pooja.gandhi

                      Hey Bin,

                       

                      The snapshot does show 'Route' in calculation 2, you did not attach your data so I just used short form of RouteDelivery. Hope that helps.

                       

                      You can modify calc 2 to show 'stopactivity' if null.

                       

                      Calc 2: IFNULL({ fixed [Activity ID]: max(IF [Seq ID] = {fixed [Activity ID]: MAX( IF [Has StopActivity or Not?] = "Doesn't Have StopActivity" then [Seq ID] end ) } then [Event Type] end ) }, 'StopActivity')

                       

                      So it becomes this:

                       

                       

                      Activity ID 4172 doesn't have stopactivity and last activity is route, activity id 4163 does have stopactivity and last activity is stopactivity.

                      1 of 1 people found this helpful
                      • 8. Re: How to count
                        Bin Xiao

                        Hi Pooja,

                         

                        Thank you soooooo much!!! It is perfectly right!!! You are awesome!!!! YOU ROCK!!!!

                         

                        Thank you very very much!!!!!

                        • 9. Re: How to count
                          pooja.gandhi

                          Hi Bin!

                           

                          I am glad it worked. You can actually just do 1 calculated field if need be, it makes things simpler:

                           

                          Max Activity: { fixed [Activity ID]: max(IF [Seq ID] = {fixed [Activity ID]: MAX( [Seq ID]) } then [Event Type] end ) }

                           

                          Drag it to filter, and click 'show filter'. If you want to see which activity ids have stop activity or not, you can just use the filter controls. You do not really need the first calculated field. Hope this helps!

                           

                          Pooja.

                          1 of 1 people found this helpful