Manipulating Tracking Data

[Anonymous]

Hi

I have a table like the one below:

ID              Status

1                Old

1                Started

1                New

2                Started

2                New

3                New

4                Old

4                Partially Done

4                New

Basically what I want to be able to do is to see which IDs passed through the status 'Started' and are now at 'New'

So in my case, ID 1 and 2 would fit that criteria.

I can't find the right query to do this, I can only find the previous status but I need to look through all historical statuses.

Any guidance would be much appreciated.

Thanks

Victoria

[Pragati11 · Accepted solution]

HI @Anonymous ,

As you haven't mentioned the desired output, I have created a measure as follows to identify the IDs in the data which have "New" and "Started" as their statuses:

1. GREEN highlight - This is the sample data used

2. RED highlight - This shows a table with "testCheck" measure using which it only displays the IDs with "New" and "Started" statuses

3. BLUE highlight - Measure created to achieve this.

If this helps and works please give a Kudos and mark this as a solutions! 🙂

Thanks,

Pragati

[Anonymous]

If you want take count
Simply create measure
Measure
Var tab=summerize(table,table[id],"new", calculate(count(table[id]),filter(table,table[status]="new")),"started",calculate(count(table[id]),filter(table,table[status]="started")))
Return
Sumx(tab,if([new]>0 && [started]>0,1,0))

Thanks
Pravin