These are chat archives for DataBrewery/cubes

16th
Jun 2017
Jonathan Drake
@drakej_twitter
Jun 16 2017 00:37
is there a best practice for doing more advanced aggregations on top of the browser.aggregate() result?
e.g. i want to calculate the difference between subsequent rows via a window function:
{
                    'name': 'revenue_change',
                    'expression': "(lead(coalesce(sum(pl.revenue), 0)::integer, 1, 0) OVER (ORDER BY month_end DESC) - coalesce(sum(pl.revenue), 0)) AS delta"
                }
Jose Juan Montes
@jjmontesl
Jun 16 2017 17:54
Not built-in. Personally in my client app(s) I always lay result cells on a table, and I do those calculations on top of such tables.
I don't know if there is some standard for that at any level. In the OLAP materials I've read that need is not usually even mentioned, or is left up to the reporting/visualization tool. Also afaik standards like MDX don't contemplate that explicitly, besides allowing subqueries.
Jose Juan Montes
@jjmontesl
Jun 16 2017 18:01
MDX is more expressive though. You can usually do those things.
Jose Juan Montes
@jjmontesl
Jun 16 2017 19:06
I prefer the simpler cubes+custom calculations approach though.
Jonathan Drake
@drakej_twitter
Jun 16 2017 19:31
Ok thanks. I may fork cubes and allow the sql statement to be wrapped
Jose Juan Montes
@jjmontesl
Jun 16 2017 21:20
:-)