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
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)
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.
@Cyril_MTL_Analy Thanks! I was able to achieve it with a similar formula.
row()-match(${patient.patient_id},${patient.patient_id})+1
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:
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.