Showing results for 
Search instead for 
Did you mean: 

How display on chart calculated columns

Former Member
0 Kudos

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:



2-T006307_2-T006308 86400686402LOW
5-T010825_5-T010826(null)586300Data 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,


Accepted Solutions (0)

Answers (1)

Answers (1)

Former Member
0 Kudos

Try Using Context Operator Foreach....

Former Member
0 Kudos

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 (%)....

Active Contributor
0 Kudos

Hi Giorgio

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

see pp 21 and  ff