How to do Window Aggregation calculation?

Shuvanakr_Mondal
2 days ago
5replies
Answered

I would like to know how we can create Window Aggregate calculations such as SUM(Sales) Over(Partition by Invoice Number) can be done in Pyramid Analytics.

Fore reference, please see the grid in the attached image. In this grid, one Invoice may have multiple Line Items. I want to calculate the Invoice Level Total (the highlighted one). I want this to be calculated while all these fields are present in the grid.

grid

- VP Product Management
- Ian_Macdonald
- 2 days ago
- Reported - view
Hi

We don't have a context calculation as a simple SUM for which you can adjust the window over which it operates.

However, there is no need as this can be easily created using a simple Formulate calculation.

Generally when you create a Measure, it will be evaluated in the the context of your query. In your example for each Line Item in the Invoice. What we need to do is tell Pyramid to ignore the Invoice Status, Line Item No., Line Item Type and only Aggregate for Invoice Number and Date or alternatively consider all Invoice Status, Line Item No., Line Item Type values for each Invoice Number and Date.

Create a new Formulate and add the Data Point block to the canvas. Select your Measure, Sales, then for each of Invoice Status, Line Item no and Line Item Type, check the All checkbox. this will effectively tell Pyramid's query engine to aggregate all the members in that dimension, even if they are in the query as separate elements.

When this Measure is used in your grid, it will aggregate by Invoice Number and Date, but not for the other dimensions in the query:

Hope that helps!

Ian
- Shuvanakr_Mondal
  
  yesterday
  
  Reported - view
  Thank you this helps. But I need one clarification. So such calculations are dimension-aware calculations. What I actually want to know is: Can I have one calculation that just does the Invoice-level total as shown in the above grid, irrespective of what dimensions I put in the row in that grid?
  
  Thanks,
  
  Shuvankar
- samuel_alma
  
  yesterday
  
  Reported - view
  If you want to "ignore" the other dimensions, then you need to add the "ALL" level to your formula - so it ignores the context of those other dimensions in your query. This is classic dimensional/semantic formula design.
  
  If you have lots of attributes to ignore this can be tedious although its the more correct way to handle it. Using the semantic approach, the value can be sliced and diced irrespective of the report or use case without breaking, making it far more robust than the typical "window" formula approach, which is more delicate.
  
  Another way that we have handled it is by creating a virtual table in the semantic layer that is a copy of the original (you can add custom SQL queries into the semantic layer without materializing a new table or view, that just references the original table). Add a new measure for the summed invoice amount, but only wire up those dimensional columns/table you want for splitting it out. So if you remove line number attribute join from this table, then this new measure cannot be broken out by line number when used in the query.
- Redeemed from the Dark Side (Power BI)
- Obee1
- 2 days ago
- Reported - view
For the record, there are "Window" functions for context calculations - which is a specific family of calculations that are dependent on how they are deployed in a give report. You can see them here.
- Shuvanakr_Mondal
  
  yesterday
  
  Reported - view
  Yes, thanks. I went through it but none of these context calculations fulfilled my need. The best match of cumulative sum, but this was not what I was looking for.
  
  Thanks though.