Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

cumulative count based on a key.

 

Hi there,

 

I have a 'collect key' column and want to provide a number (in sequence) next to it for all trips that have the same key, and for this to repeat for all of the different keys. 

What this is showing is a transport companies trip details.

i.e - "key = xxx"  the first-row occurrence would return 1, the second 2, third 3

- "key =yyy" the first-row occurrence would be 1, second 2 ...etc.

 

I guess what I am after is a cumulative count for each key.  

 

I am unsure of how to upload data but below is a sample.. i am looking to create a column that provides information for the right column

 

collect key accumulated
11/9/2018TTLCTemuka CT - Clandeboye DS Grid1
11/9/2018TTLCTemuka CT - Clandeboye DS Grid2
11/9/2018TTLCTemuka CT - Clandeboye DS Grid3
11/9/2018TTLCTemuka CT - Clandeboye DS Grid4
11/9/2018TTLCTemuka CT - Clandeboye DS Grid5
11/12/2018TTLCTemuka CT - Clandeboye DS Grid1
11/12/2018TTLCTemuka CT - Clandeboye DS Grid2
11/12/2018TTLCTemuka CT - Clandeboye DS Grid3
11/12/2018TTLCTemuka CT - Clandeboye DS Grid4
11/12/2018TTLCTemuka CT - Clandeboye DS Grid5
11/13/2018TTLCTemuka CT - Clandeboye DS Grid1
11/13/2018TTLCTemuka CT - Clandeboye DS Grid2
11/13/2018TTLCTemuka CT - Clandeboye DS Grid3
10/26/2018TTLCSCS Timaru1
10/26/2018TTLCSCS Timaru2
10/26/2018TTLCSCS Timaru3
10/26/2018TTLCSCS Timaru4
10/26/2018TTLCSCS Timaru5
10/26/2018TTLCSCS Timaru6
10/26/2018KR-2HJM14SCS Timaru1

 

 

Any help is greatly appreciated.

 

 

 

Thanks, Shem

 

 

 

 

Need Help
  • AlB's avatar
    AlB
    7 years ago

    If you can have an extra column with a unique, consecutive numerical ID (index), I think the code below should work. You can add an index column easily in the query editor (see the solution on this post)

     

     

    Table[Accumulated] =
COUNTROWS (
    FILTER (
        Table;
        Table[Collect Key] = EARLIER ( Table[Collect Key] )
            && Table[ID] <= EARLIER(Table[ID])
    )
)

     

5 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    Hi Anonymous,

     

    Do the rows you want to number (with same collect key) have exactly the same values on each column (as it appears from the attached image)?

    Does each row have a unique identifier (like a row number)?

    If you attached a sample with the table it would probably be easier for people to help.

     

    .

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      I am unsure of how to attach a sample. Hopefully, the amended post will suffice.

       

      Thanks, Shem 

      • AlB's avatar
        AlB
        Community Champion
        7 years ago

        If you can have an extra column with a unique, consecutive numerical ID (index), I think the code below should work. You can add an index column easily in the query editor (see the solution on this post)

         

         

        Table[Accumulated] =
COUNTROWS (
    FILTER (
        Table;
        Table[Collect Key] = EARLIER ( Table[Collect Key] )
            && Table[ID] <= EARLIER(Table[ID])
    )
)