Down Totals - What am I doing wrong?

Samir Jones

Hello,

What am I doing wrong here?

In Block D, I want to display the Down Total from Block C.

In the headers, I've written what Summary I'm using.

In Block E, I just wanted to display what happens if I change the Summary to "Calculated".

Scott Bloxsome

Hi Samir,

So the execution of algorithms on screen, including dt() functions, is done in a certain order. A dt() will provide the column total (or sub total) when it is a cube. However when working off another algorithm it essentially performs a down total on the calculation.

So in the example above it will perform dt on block c, which has the algorithm a*b, this means it's actually doing dt(a)*dt(b) for each row. At the total you can see that down total of block d is matching as it's just performing the down total on that one cell.

However, if you bring in a ranking function like cumulated value next to your algorithm column, set to summary total (if you leave it as calculated you'll get nothing), you will get the desired result:

I hope this helps you out.

Scott.

Akrem ABDELHAFIDH

Hello @Samir Jones
I think you must set the block b from NO TOTAL to AVG.

Neha Hasija

Hi Samir,

Try changing the summary algorithm for Price to TOTAL?

Samir Jones

Hi @Akrem ABDELHAFIDH & @Neha Hasija,

Thank you for your replies.

It's correct that I will get values if I use "AVG" or "TOTAL" on Price, but the values are totally irrelevant in this case.

Average shows me a Grand Total average, not a "group" average.
Even if it would show me the "group" average, it's still irrelevant since the rows would be weighed the same in a Summary average.

Using Total also gives me irrelevant values for this case. Summing up averages will not help.

From my understanding, the algorithm logic should be that Block d fetches the Down Total from Block c (hence "DT(c)"), which should populate "109 832" on each row in Block d. This should work regardless of what Summary I have on Block b, since I already have a Down Total on Block c.

Tommaso Riva

Hi @Samir Jones,

Down total for Algorithm Block looks like is not calculated correctly.

My suggestion is to apply the down total to the Revenue cube instead of the algorithm.

Regards,

Tommaso

Samir Jones

@Tommaso Riva, thank you, but I don't have a Revenue Cube.

And I don't want to connect a Revenue Cube and Procedure either, because the original Dataview is already big/slow as it is. The original is with multiple Data Entry.

I'm only showing the relevant blocks in the screenshot.

Tommaso Riva

Hi @Samir Jones,

unfortunately I'm not aware of the complexity of your data view.

Alternatively, you can always use the Nexel feature.

Of course it's not without limitations:

>Uses a cell-based approach that requires a high amount of resources detriment of performances.
>It is based on the layout execution with all the limitations related to a large dataset.

If your goal is to get the down-total by row use it, but it won't help with the performance.

Hope it helps.

Regards,

Samir Jones

@Tommaso Riva, thank you for the advice.

If Nexel will decrease the performance, then we will not go that route. :-)

I've filed a ticket to Board Support, regarding this.

IMO. If the Down Total in this case doesn't work, then the documentation of what a Down Total does, is incorrect. So I would say it's a bug.

Samir Jones

@Scott Bloxsome, thank you very much.

This solved my challenge.

The solution it self is not that obvious and can't be found in documentation.
It would be great if the the total functions (down, row, grand) would behave like it's name. :-)