Marc,

The issue that you are experiencing is related to different levels of the fields you are using in your table. Your activity table and cases table have a n:1 relationship, so when you use a simple aggregator the values will be summed for each record on the lowest level in the OLAP (which will be activity level).

You can troubleshoot by hiding each Dimension/KPI in your OLAP to find out which field is producing the error for you. After you have identified which field that is - figure our at what level that KPI is calculated, and use PU functions to bring it to the same level as your other dimensions/KPIs.

If you share the PQL behind each field I can help you identify where the discrepancy is.

Hope this helps.

Eugene

Hi Eugene,

thanks for answering; My point is indeed there: I have two different level of data to show in the OLAP: some from the case table and some based on the activity table. The count statement is not affected by the 1:n relationship between the two tables. But the sum is. And indeed, if I hide the 3 ratios, the total value shown is correct, as I wrote in my initial comment.

I just wonder if there would be a statement in SQL/PQL, or a set of, that would allow to sum distinct on the case, rather than multiplying by the number of activities...

regards

Marc

Try rewriting your ratios using PU functions that pull up the calculations to the case level, it sounds like those are the ones causing the issue.

Can you please share the PQL you are currently using for one of the KPIs?

hum, I must have overlooked something. It is only the automation rate that bugs the total value. Currently, it is the standard way:

100.0*AVG(

CASE

    WHEN "_CEL_P2P_ACTIVITIES"."ACTIVITY_EN" IN (<%= whitelist %>) THEN NULL

    WHEN ISNULL( "_CEL_P2P_ACTIVITIES"."USER_TYPE") = 1 THEN NULL

    WHEN "_CEL_P2P_ACTIVITIES"."USER_TYPE" IN (<%= auto_user_type %>) THEN 1.0

    ELSE 0.0

END

)

But the thing is that bringing the automation rate at case level doesn't make much sense as it reflects how automated the process of a single case is effectively, and then gives an overall average.

I may consider removing this KPI from the general OLAP.

You are indeed correct. The automation calc with PU to the case level will omit the activity level automation, and instead will calculate on case level. So instead of being # automated activities/# total activities it will be # automated cases/# total cases.

Please upvote if the interaction was helpful.

Thanks!

Eugene