Solved: Creating a window function inside a table calculat... - Page 2

Eran_Sagi · 03-30-2020 09:08 AM

Hey all,

I’ve searched everywhere and couldn’t find an answer

i would like to emulate a window functions in one of my looks

as you see i have 2 dimensions: created_at and merchant_id
and one measure: URLs which is a STRING_AGG function

im trying to add a second measure that counts per each created_at_time the number of merchant_id’s per this timestamp

so if it was plain SQL id write: count(distinct merchant_id) over(partition by created_at)

i’ve seen 3 posts where @izzymiller says @chris_seymour1 says its possible but he never replied 😉

Thanks!

chris_seymour1

Hey @Eran_Sagi,

It is indeed possible! Here’s some example code you can use in a table calculation:

group_start_row:
match(${orders.id}, ${orders.id})

next_group_start_row:
count(${orders.id}) - match(${orders.id}, offset(${orders.id}, count(${orders.id}) - row()*2 + 1)) + 2

You can replace orders.id with the dimension you want to group by (in this case the Created At Time dimension). From there, you can get the grouped count like this:

${next_group_start_row} - ${group_start_row}

Breaking this down, the match function gets the row number of the first row that contains the current row’s value. So if rows 7-10 contain 2020-02-20 16:40:38, the match function will return 7 for each of those rows, which gives us the group_start_row calculation.

The next_group_start_row calculation reverses the list and applies the match function to find the last row for each group, making a few adjustments in the arithmetic to get to the next_group_start_row from there. In this case it should return 11 for rows 7-10, since that is the first appearance of the next timestamp, 2020-02-20 16:40:37.

Once you have the starting rows of the current group and the next group, you can simply subtract the two to get the grouped count.

Let me know if you have any questions!

View solution in original post

Creating a window function inside a table calculation/custom measure