How do you get the column sum of a formula into a label?

Julien Blankestijn

Hello everyone,

I want to see the sum of a column in a label, like in the screenshots below. The problem with this: The column contains a formula. Does anyone have an idea? The blocks are exactly the same in both layouts. If you need a cube-structure feel free to let me know.

Thank you in advance
Julien

------------------------------
Julien Blankestijn
Junior Controller
VOK DAMS Events GmbH
Germany
------------------------------

Oliver Weichselsdorfer

Hi Julien,

it should be work if you klick at the block D, than you see at the right side the block view. Here you can switch to sum (instead of calculated). My example shows Block E

Regards

Oliver

------------------------------
Oliver Weichselsdorfer
BI Projektleitung
hostettler ag
Switzerland
------------------------------
-------------------------------------------

Julien Blankestijn

Hi Oliver,

thank you for your response.

Unfortunately, using sum instead of calculated does not change the result. Block D in the Data View also uses calculated, because i need to get the result of dividing the sum of block b and c. If I use the sum in block d in the Data View, I get the same result as in the layout in both cases. Changing Block b in the layout to total does not change the result either.

Regards
Julien

------------------------------
Julien Blankestijn
Junior Controller
VOK DAMS Events GmbH
Germany
------------------------------
-------------------------------------------

Brendan Broughton

Hi @Julien Blankestijn

Does the layout in the Label have an entity 'by row'?

I'm not sure if I understand the issue correctly, so apologies if this isn't relevant...but from how I understand your problem this is what I'd try (on screen, without going down the dataflow route to populate a cube with your algorithm

Add 2 more algorithms, one as E= dt(b), the other as F= dt(c)., then change your D algorithm to E/F.

That will hopefully give the answer you need to display on every row in column D.

Now that you have the value on every row in D, it is just a matter of working out the best way to display just one of those values in the label.  You could 'map' a single value to a new block, or I'd try either getting the 'average' of that block (which would be the same if every value is the same), or the sum of that block divided by a count, into a new block which you'd then display in the label.

The other option would be to populate algorithm B into a cube, then use that in the DV rather than Algorithm B, that should allow you to NOT have to display an entity by row which should make the total calculation work.


dt is here BOARDHelp

------------------------------
Brendan
------------------------------
-------------------------------------------

Julien Blankestijn

Hi Brendan,

thank you for your response.

Indeed, You understood my issue correctly and Your idea brings us closer to the solution.

But, unfortunately, using dt to return the column total in each row does only work for dt(c), with the algorithm taking into account a cube. In the case of dt(c), where the algorithm refers to another algorithm, this does not seem to work, as shown in the screenshot below. 

Using Block Reference in block c or e does not work either.

Best Regards
Julien

------------------------------
Julien Blankestijn
Junior Controller
VOK DAMS Events GmbH
Germany
------------------------------
-------------------------------------------

Jason Febo

You are going to need to materialize block b into a cube itself using a dataflow.  Then use that cube as block b instead.

------------------------------
Jason Febo
Architect
Metrix Consulting Solutions, LLC
United States
------------------------------
-------------------------------------------

Brendan Broughton

Well that's a pain isn't it!  Sorry it didn't quite get you there.  As mentioned, the other option is to populate as a cube...OR you can get the equivalent of the dt() working with nexel using a sum.range.

I'm not entirely sure what else is going on your screen/layout so no guarantees that Nexel will work for your exact scenario, but it is where I'd look if you want to avoid populating a cube as it will give a sum of your algorithm.

------------------------------
Brendan
bbroughton@bi5.com.au
Australia
------------------------------
-------------------------------------------

Etienne CAUSSE

Hi @Julien Blankestijn
did you try to reproduce the layout in another dataview, then use the option "show only grand total", and customize the appearance of the dataview so that it looks just like your label ? Such as hide headers, etc. ?
Etienne

------------------------------
Etienne CAUSSE
Finance Project Manager
France
------------------------------
-------------------------------------------