# Count number of times 2 items are in the same transaction

Here’s a sample of data. I want some help to either build a calculation or setup an analysis filter that will tell me how many times Transfer and Stop occur in the same Xaction ID. In this scenario it is 2.

Index Xaction ID Xaction
1 1 Start
2 1 Stop
3 2 Start
4 2 Transfer
5 3 Start
6 3 Transfer
7 3 Stop
8 4 Start
9 4 Stop
10 5 Start
11 5 Transfer
12 5 Stop
13 6 Start
14 6 Transfer

**|5 |3 |Start|
**|6 |3 |Transfer|
**|7 |3 |Stop|
|8 |4 |Start|
|9 |4 |Stop|
**|10 |5 |Start|
**|11 |5 |Transfer|
**|12 |5 |Stop|
|13 |6 |Start|
|14 |6 |Transfer|

When filtering I tried to filter by all Xaction ID’s then by Transfer and Stop but nothing was returned. I haven’t been able to come up with a calculation that works.
Thanks

Hi @Stapper ,

Welcome back to the community!!

For this use case, I would create a calculated field to capture only stop and transfer action and use a distinct count over at xaction id to check if it is 2.

Step 1: action_needed= ifelse(xaction=‘Stop’ or xaction=‘Transfer’, xaction, NULL)
step 2: count_action = distinctCountOver(action_needed,[xaction id])
step 3: action_id_list= ifelse(count_action=2,xaction id, NULL)
step 4: count of id= distinct_Count(action_id_list)

The calculations are level aware and may need tweak based on the data granularity.

Thanks,
Prantika

1 Like

Thank you @prantika_sinha, this appears to be the answer I’m looking for. I did not think of breaking it up into pieces. Such a beginner’s mistake. You’re correct in that I will have to tweak the formulas. I haven’t changed the granularity because I’m only familiar changing it in visual filters. On step 2, I used PRE_AGG and PRE_FILTER but keep getting errors. I think it’s because of the granularity you mentioned. I’m trying to figure it out.

PRE_AGG error: Expression parseDate({date}, ‘dd-MM-yyyy’) for function parseDate has incorrect argument type parseDate(Date, String). Function syntax expects String, String, …

PRE_FILTER error: Expression concat((value), ‘%’) for function concat has incorrect argument type concat(Decimal, String). Function syntax expects String, String, String, …

Thank you so much for the answer and quick response.

1 Like

Were you able to clear the error? Neither of them look aggregation issue, but input datatype mismatch.

This refers to {date} is already a date field and not a string, so unable to convert it to date. You can try replacing the parseDate function block with {date} and check.

This is about {value} is not a string so it is unable to concat, so you can try concat(toString(value),‘%’))

One quick point to add, PRE_AGG and PRE_FILTER both act differently and you may need to check based on your use case what fits in.