Formula Collision in the result rows

[Former Member]

Hi Guys,

I have 2 KFs A & B and KF C = A/B.

In Results rown instead of calculating it as ( A1 + A2 +... +An)/ (B1 + B2 .... Bn) , I want to calculate it as (C1 + C2 + ....+Cn). I tried all options in formula variables, it simple doesnt work. Not sure if we can use it with Result Rows.

Does anyone have any answer ?

I would return point for all the helpful answers which I always does.

Cheers

Kirk

[Former Member]

Hi Murali,

Congrats on your 1000 score.

Raj

[Former Member]

Hi Kirk,

There are 2 ways of doing this

1) If you're defining the Caluclate KF then you have to right click on the calculated key figure and then click on Enhance button. In the Aggregation block you have to choose "After Aggregation" from the drop down.

2) If you are using New formula which is local to the query only, in that you have to define a new formula as per your requirement and then go to the property of the new formula by right clicking on it. In the Formula collision block you ned to choose "Result from competing formula".

Hope this will help.

Please note that for the calculated field C which is A/B the Calculate Result as should be Nothing defined. But for Field A and B it should be SUMMATION.

Give reward point if satisfied.

Regards,

Message was edited by: tapan tripathy

[Former Member]

Tappan,

Thanks for the reply.

I am usinf formula in the query and tried using both the options in formula collision. Nothing has changed. The query still calulate it in the same way.

One question I have though is if we can set formula collision in result row. I doubt if we can.

Cheers

Kirk

[Former Member]

Hi Kirk,

To get sum of C1,C2,C3...just go to the formula properties, and choose these:

1.calculate result as : 'summation'.

2.use default direction.

3.Formula collision: nothing defined.

I checked it out and it worked...

Hope its clear..

Shylaja.

[Former Member]

Hi Kirk,

Shylaja is right, that's the correct setting. Other suggestion is creating a calculated key figure. In properties you have to define "Before Calculation" as "Time of Calculation".

Best regards,

Björn

[Former Member]

Thanks Shylaja,

I think you are missing a point. The point is even if i set it on calculate result as 'summation', when I take characteristic out of drill down it calculates KF C from aggregated values.

I dont want it to happen.

Cheers

Kirk

[Former Member]

Hi Kirk,

please define a calculated key figure as I described before. Now you are able to remove characteristic from drilldown and it is still showing the correct result.

Best regards,

Björn

[Former Member]

Thanks Bjorn for your help. I know it works wiht simple formulas. But its disabled and always set to After Aggregation for complez formula.

Anyways I am still awarding you point for this.

Cheers

Kirk

[Former Member]

Kirk,

I have doubts that it's working with formulas... If you remove your characteristic from drilldown, the row isn't result row any longer, it becomes a simple data row. But for data rows, you wish C to be calculated by A/B.

Best regards,

Björn

[Former Member]

Bjorn,

Its not working I am afraid and can't simply work. We have decided to build another ODS on the Cube to store aggreagated records from which the query will fetch the data.

Cheers

Kirk

[Former Member]

Hi Kirk,

You said you tried all options with formula variables. But, just to check, did you try to create a formula like this C = Overall Result A / Overall Result B? It would look like this SUMGT(A)/SUMGT(B). Do you want to hide the underlying rows again? If so, try the process I suggested for hiding columns in WAD.

Hope this helps,

Regards,

Petter

[former_member188325]

why are going for formula variables?don't understand really.

to get c1c2...Cn ,in the properties of C set <b>'calculate result</b> as...' to 'summation'.

hope you have set calculate single values as...to nothing defined and didnt choose formula collision!