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

How to count

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

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

• 2. Re: How to count

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

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

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

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

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.

best,

• 7. Re: How to count

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

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

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