count repeated values to get sequential order of occurrences

Looker Q&A Looker
1

I am a basic user and only have limited access to create custom dimensions and table calculations. Would it be possible with LOOKER to create count order of occurrences?

I can achieve this in excel via COUNTIF($a$2:a2,a2)

I’m trying to replace a date dimension with with 1st occurrence, 2nd occurrence, etc.

example: https://i.stack.imgur.com/0RokE.png

2

Hello @genghisk ,

you can do that with this piece of code in a table calculation:

if(match(${order_items.created_date},${order_items.created_date})=offset(match(${order_items.created_date},${order_items.created_date}),-1)
 , 1+row()-match(${order_items.created_date},${order_items.created_date})
 , 1)

image.png
image.png1966×802 96.2 KB

Just replace order_items.created_date by your own date dimension.
Also note that this only works if the sorting is done on that date dimension.

2 Likes
3

@Cyril_MTL_Analy Thanks! I was able to achieve it with a similar formula.

row()-match(${patient.patient_id},${patient.patient_id})+1

image.png
image.png472×357 32.7 KB

My next challenge, is to pivot that “rank/order” column, which is not possible with table calcs, so each row will be a unique ID#, I am assuming this can only be done on the back end of the database with facts table and such.

example:

image.png
image.png540×203 5.1 KB

4

Nicely done!

As per your question around pivoting table calc, I’m afraid you’re right, Table calc have their limitations.

That will have to be done in LookML.