Table Calculation Query (aggregate numbers within custom date range)

Hi everyone,
I am looking for some ideas on custom table calculation and really appreciate your help!

I used diff_day functions as filter to select Post URI that have been created for a certain numbers of days (differ_day (Created Date, Traffic Data Date). Now I would like to custom a table calculation that can aggregate Total z_pv from Created Date to Traffic Date Date for each Post URI.

Could anyone share some ideas on how should I create such a table calculation?

Screen Shot 2020-07-08 at 1.43.22 PM|690x79

Many thanks in advance~

0 1 760
1 REPLY 1

I’ll say that this sounds like something that’d be better done in the LookML, if you have access to it.
Generally, table calculations stink at anything related to aggregation, since in order to refer to a value they need that value to be in the result set, and that means the results cannot be aggregated very well. This wouldn’t be that crazy to build in LookML so let us know if you have access to do that!

In a table calculation… Unless I’m misunderstanding, this is basically a window function— You want to aggregate values within certain buckets across the result set. Window functions are possible in table calculations. Check out this reply for an idea of how to do it:

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} Brea…

Give that a shot using Post URI instead of orders.id to group.

Top Labels in this Space
Top Solution Authors