Is it possible to change this reverse algorithm logic?

Marco Poppi · 2023-03-20T14:05:25+00:00

There was an error rendering this rich post.

Hello everybody,

I would like to know if you have any tip in order to “invert” the update logic behind this dataview and its reverse algorithm.

In particular, I have 3 simple columns: Budget Value (a), Quantities sold (c) and Average Price (which is an algorithm a/c). Here the reverse algorithm for block b (avg price) is defined as follow: c=a/b. All of mentioned columns has the data entry enabled.

What I obtained at this point is: if I change the quantities or avg price, the average price or quantities will be proportionally affected; and If I change the Budget Value the Average price will be affected.

What I'm struggling to achieve (my goal) is: if I change the quantities or avg price, the average price or quantities will be proportionally affected; and If I change the Budget Value the quantities will be affected.

Can you help me? I need the average price to be an algorithm because i have not the leaf dimension in row and otherwise i will get the sum not the weighted average. Not having the leaf is a requirement.

Find more posts tagged with

Accepted answers

Daniele Santandrea

Hello Marco Poppi,

you cannot achived your goal with this configuration.
In fact, in your case each change impacts the other factors.

The configuration is as follows:

where:

A = cube copy of Budget Value
B = cube copy of Qty
C = Budget Value
D = Avg Price = A/B (reverse algorithm A=B*D)
E = QTY = C/D (reverse algorithm D=C/E)

In this way:

change Budget Value —> change Qty
change Avg Price —> change QTY
change QTY —> change Avg Price

Regards,
Daniele

All comments

Valentina Sarti

Hi Marco,
As you have already experienced, since the Average Price is calculated as an algorithm, there is no way with this specific layout to avoid an impact on it by changing the Budget Value.
A workaround, that we have already used, is to previously save the Average Price in a cube by Line of Business, and then create a screen with 3 different tabs where to change one metric at a time: in every tab you will have a different metric as an algorithm (in the first one the Budget Value, in the second one the Average Price, in the third one the Quantity), and under the dataviews you will need to trigger dedicated procedure in charge of updating the other metrics.

Of course, in this way it will not be possible to change both the Macro Area level and the Line of Business. If you need even this functionality, a solution could be to create a different Average Price cube by Macro Area.

Hope this helps!

Daniele Santandrea

Hello Marco Poppi,

you cannot achived your goal with this configuration.
In fact, in your case each change impacts the other factors.

The configuration is as follows:

where:

A = cube copy of Budget Value
B = cube copy of Qty
C = Budget Value
D = Avg Price = A/B (reverse algorithm A=B*D)
E = QTY = C/D (reverse algorithm D=C/E)

In this way:

change Budget Value —> change Qty
change Avg Price —> change QTY
change QTY —> change Avg Price

Regards,
Daniele

Marco Poppi

@Daniele Santandrea @Valentina Sarti Thank you both.

Valentina you are saying what I was afraid to hear. I think I would go for a solution similar to that you are suggesting, while trying to obtain actually what is my "visual" goal.

I could create 2 different Data View with the same dimensions in row/column (the second one without rows in visualization), link their scrollbar and paste one next to the other (the purpose is that they look like the same DV). The first will contain only the budget cube with a procedure triggered on the data input which update the quantities, the second one will be exactly the same that I shared but the budget hidden.

I need to test this, hoping that when launching the procedure in the first DV the second one will be affected only on the quantities column while leaving the average price fixed.

Daniele Santandrea

Hi Marco Poppi,

in my first answer there is a solution with a single dataview (you should create only 2 new cubes) and each column has a Data Entry.

In this case you have:

Budget Value = Cube
Avg Price = Algorithm
Qty = Algorithm

At the end of the step, if you want to save the Qty value in a specific cube, you can perform a procedure with a Dataflow.

Let me know if anything is not clear.

Regards,
Daniele

Valentina Sarti

https://community.board.com/discussion/comment/14220#Comment_14220

Hi Marco,

from Daniele's answer you can find a solution that allows you to keep the same visualization that you proposed, but for sure in both the cases (Daniele's one and mine) the conclusion is that you need new cubes and procedures to manage this situation. It is for sure not possible to manage it only through one algorithm.

Concerning the solution that I've proposed, in order to affect the right measure at a time, you will not have metrics depending on algorithm and it will be necessary to design different procedures, not only one. In this way you can address the right change in the cubes, basing on which are the measures that have been changed manually in the dataviews.

Marco's solution is in the same direction, but avoiding to create different dataviews.

Hope this is clear.

Valentina

Marco Poppi

@Daniele Santandrea I have tried your solution and it works. I should add either a procedure triggered by the data entry or a final confirmation procedure to copy-paste values from the algorithms into the cubes now, but I wanted to avoid it thinking about performance issues so preferring to write directly in the cubes… I guess it is not possible

Daniele Santandrea

@Marco Poppi i suggest you create a final confirmation with a simple data flow (from Qty algorithm to Qty cube):

QTY = Budget Value / Avg Price = Budget Value / (copy Budget Value / cube copy of Qty)

Why do you think this solution has performance issues?

Regards,
Daniele

Marco Poppi

I was thinking about extracting the layout or the dataset and loading the algorithms into the cubes, also your suggestion is correct and even faster. I agree it is a simple DF, but still I won't have a direct input in the cubes and users are already experiencing performance issues (on a previous version of the application not developed by me). I would go for a (cloud) machine upgrade in case of further slowdowns .

@Daniele Santandrea I thank you for the answer, in any case you really helped me :)

Daniele Santandrea

Ok, it's clear Marco Poppi.
I am available for any clarification.

Bye,
Daniele

Daniele Santandrea

Hello Marco Poppi,

you cannot be achived your goal with this configuration.
In fact, in your case each change impacts the other factors.

The configuration is as follows:

A (HIDE)	B (HIDE)	C (CUBE)	D (ALG)	E (ALG)
VALUE-BIS	QTY-BIS	VALUE	AVG Price	QTY
2000	200	2000	10	200
1000	500	1000	2	500

where:

A = cube copy of Budget Value
B = cube copy of Qty
C = Budget Value
D = Avg Price = A/B (reverse algorithm A=B*D)
E = QTY = C/D (reverse algorithm D=C/E)

In this way:

change Budget Value —> change Avg Price
change Avg Price —> change QTY
change QTY —> change Avg Price

Regards,
Daniele

Daniele Santandrea

Hello Marco Poppi,

you cannot be achived your goal with this configuration.
In fact, in your case each change impacts the other factors.

The configuration is as follows:

A (HIDE)	B (HIDE)	C (CUBE)	D (ALG)	E (ALG)
VALUE-BIS	QTY-BIS	VALUE	AVG Price	QTY
2000	200	2000	10	200
1000	500	1000	2	500

where:

A = cube copy of Budget Value
B = cube copy of Qty
C = Budget Value
D = Avg Price = A/B (reverse algorithm A=B*D)
E = QTY = C/D (reverse algorithm D=C/E)

In this way:

change Budget Value —> change Avg Price
change Avg Price —> change QTY
change QTY —> change Avg Price

Regards,
Daniele