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?
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:
[Creating a window function inside a table calculation/custom measure](https://discourse.looker.com/t/creating-a-window-function-inside-a-table-calculation-custom-measure/16973/2) Table Calculations
Give that a shot using Post URI instead of orders.id to group.