Use Other columns rather then [Value] after THEN keyword

[Michele Scarpa]

Hello,

I am trying to calculate the ration of cumulative sum of two columns. For one of them I have to use all values while for the other I just need the first value (This is needed as I am coloring by a hierachy) .

So I need to pick the first value and the calculate the post aggregation like:

First([Column A]) THEN Sum([Column B]) OVER (AllPrevious([Axis.X])) / Sum([Value]) OVER (AllPrevious([Axis.X]))

Unfortunately after THEN I can only use the result of firs aggregation.

Is there a way to proceed?

Thanks

Michele

[Gaia Paolini]

could you show a sample dataset and/or view of what you are trying to achieve?

[Michele Scarpa]

Hi,

basically I want to divide the value of the top graph with respect to the values on the bottom. The numerator is the cumulative sum (Sum([Value]) OVER (AllPrevious([Axis.X]))) of Failures while the denominator is the sum of unique value of System Time per each system (First([Column A]) THEN Sum([Column B]) OVER (AllPrevious([Axis.X]))). It is colored by a hierarchy (System + Component) and can be whose level can be modified by a slider. That is why I need to divide the behaviors between numerator and denominator.

Yuo can find an sample dataset attached.

Thanks

[Gaia Paolini]

what is the top graph and what is the bottom graph?

[Michele Scarpa]

According to the plot of which I shared the screenshot, the top par shows the cumulative sum of Failures(Sum([Value]) OVER (AllPrevious([Axis.X]))) while the bottom shows The cumulative sum of time (Sum([Column B]) OVER (AllPrevious([Axis.X])))). The time is equal for each component installed in the same system and I need to take just one of them per node.