Is it possible to sum an average in a cube?

A
Rank
Userlevel 3
Badge +14

In a cube I would like to have the average of columns (e.g. year, quarter, month), totalized in the rows (e.g. group, device). How could I achieve this? 

11 replies

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Andre,

It is a bit unclear to me what you are trying to achieve.

For example, do you mean that the 2020 Average should be used for the 2020 Sum? E.g. Average is here 4.01 over 2020 for let's say 50 rows, so the 2020 Sum should then be 200,50?

 

"Wake me up before you low-code"
A
Rank
Userlevel 3
Badge +14

Hi Mark,

In the cube below the columns show the average in time. When I add a sum for the rows, I would like to show the sum of the average.  So for the first column I want a total of (8+48+88+12+850).

 

I also tried a cube field calculation with the DevExpress syntax, but I didn't find the right formula. 

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Andre,

It's not possible to create a Total for a Value column; only for Data columns. The way of achieving your goal is by calculating the average in the underlaying View (with a windowed function) for 1 row per Cap… (with Lijmen/Bankwerken etc.) and summing that column.

I hope that answers your question.

"Wake me up before you low-code"
A
Rank
Userlevel 3
Badge +14

Hi Mark, 

It's not quite what I'm looking for, because the windowed function is based on a certain combination. If I use an average per week or period it will still totalize per year. 

Thanks for your response. 

 

 

A
Rank
Userlevel 3
Badge +14

Hi Mark, 

I contacted DevExpress to ask if they support a grand total of average values. It seems that this is possible. There are 2 types of custom totals:

  1. calculateCustomSummary  (Specifies a custom aggregate function) 
  2. calculateSummaryValue (Specifies a custom post-processing function for summary values)

I think Thinkwise is using the first one for data-fields. The second one can be used to have a grand total of average values. 

The great news is that the Thinkwise model already supports entering a custom total for value columns. The only problem is, it does nothing :-)

  

 

 

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Mark, 

I contacted DevExpress to ask if they support a grand total of average values. It seems that this is possible. There are 2 types of custom totals:

  1. calculateCustomSummary  (Specifies a custom aggregate function) 
  2. calculateSummaryValue (Specifies a custom post-processing function for summary values)

I think Thinkwise is using the first one for data-fields. The second one can be used to have a grand total of average values. 

The great news is that the Thinkwise model already supports entering a custom total for value columns. The only problem is, it does nothing :-)

  

 

 

Good that the DevExpress component supports this, but I do think we'll need to adjustments to the Windows and Web GUI and the Software Factory for this. Feel free to create an idea for this :thumbsup_tone1:

"Wake me up before you low-code"
A
Rank
Userlevel 3
Badge +14

Hi Mark,

Still a question about the implementation of custom totals in the SF. The documentation is not very clear about how to implement this.

It seems that custom totals only work if you enter them for dimensions. The only way to do this, is on the ‘cube view’-level. On the cube view field level the tab is ‘read only’ for dimensions. 

On the cube view field level, you can also enter totals. This tab is only available for ‘value’ fields. You can even show totals as ‘Custom' (see below). 

The total on cube view field level does not result in a ‘custom’ total.

How should I use this? 

 

Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Andre,

I never worked so much with Cubes but I would guess setting this to Custom would allow the user to create totals for the column, but I might be wrong. I will try and find out what this exactly does.

"Wake me up before you low-code"
poonam
Rank
Userlevel 3
Badge +2

Hi @Andre te Raa ,

we have looked into this issue and it is indeed at the moment not possible to show sum totals of column averages in cubes. We will add this as a work item into our backlog to implement in the near future.

Poonam
Freddy
Userlevel 5
Badge +14

Hi Mark,

Still a question about the implementation of custom totals in the SF. The documentation is not very clear about how to implement this.

It seems that custom totals only work if you enter them for dimensions. The only way to do this, is on the ‘cube view’-level. On the cube view field level the tab is ‘read only’ for dimensions. 

On the cube view field level, you can also enter totals. This tab is only available for ‘value’ fields. You can even show totals as ‘Custom' (see below). 

The total on cube view field level does not result in a ‘custom’ total.

How should I use this? 

 

@Mark Jongeling Did you ever got an answer to this?  Can we create custom totals? 

In my situation I have a data value that shows an average price per kg. In the total field I want the weighted average price per kg.. not the average price per kg of the aggregated average price per kg (which it does by default). 

How does the show totals custom work?  Can I use it to get a weighted average? 

LEF - The future favors the brave. [lef.tec.br]
Mark Jongeling
Rank
Userlevel 7
Badge +21

Hi Freddy, I believe your situation is currently not possible. Maybe @poonam can confirm that.

Also what the Show totals Custom exactly does is not clear for me either. Looking through the Windows GUI code, I can't find anything related to it (with my limited knowledge of .NET). It may be an added option that is solely for the editability of totals inside the GUI itself, but I don't know for sure.

"Wake me up before you low-code"

Reply


Terms & ConditionsCookie settings