How display on chart calculated columns

[Former Member]

I have a table with:

Date, ID, [Counter]

I queried ID and [counter] filtering Date between Date1 and Date2 that results in:

ID, Sum([Counter])

Depending on the number of days of available data I defined a Threshold ( [numberOfDays]*0.9*86400) and a Root that defined the ID as LOW or HIGH or Data N/A (in case of null values).

The final result is something like this:

ID	Sum([Counter])	numberOfDays	Threshold	Root
2-T004069_2-T004070	86392	5	86300	HIGH
4-T002255_4-T002256	86400	6	86402	LOW
4-T003189_4-T003190	86399	6	86402	LOW
2-T006307_2-T006308	86400	6	86402	LOW
5-T009977_5-T009978	86399	5	86300	HIGH
5-T010825_5-T010826	(null)	5	86300	Data N/A

I need to create a pie-chart with the percentage of ID HIGH, ID LOW and ID without data.

I've created a Dimension variable with the 3 Root value ( If(IsNull(Sum([Counter]))Then "Data N/A" ElseIf(Sum([Counter]>=Threshold) Then "HIGH" Else "LOW" ).

I've created a Measure Counter ( Count(ID) )

The expected result is a table:

Root                                   Count

Data N/A               1

HIGH                    4

LOW                                        1

I'm receiving the following:

Root                                   Count

Data N/A                    1

HIGH                                    0

LOW                                    5

It seems that It recalculates on the fly the Threshold aggregating all days queried instead for each ID. In the example the Threshold on the fly is 86402.

Where is the fault? Can you show me step by step the procedure? I wonder why It's recalculating the threshold corrupting the final Count getting   from query not from report result.

Thank you for your help,

GreenRobot

[Former Member]

Try Using Context Operator Foreach....

[Former Member]

Thanks for your reply Kiruthika!

I've modified the Measure Counter ( Count(ID) ) in Measure Counter ( Count(ID) ForEach([ROOT])) , but the result is the same.

It's still recalculating the Threshold as static ( number of days for which the report has been run) instead of dynamic way ( each ID is present for different days in the period for which the report has been run ).

I hope my problem is clear:

I need to plot how many ID are HIGH (in %) , how many are LOW (%)....

[former_member188911]

Hi Giorgio

It looks like you need to force the calculation context, please check the manual  Using functions, formulas and calculations 

http://help.sap.com/businessobject/product_guides/boexir4/en/xi4sp5_ffc_en.pdf 

see pp 21 and  ff

Thanks

Simone