Panel coverage calculation issue

[rrodriguesrr82]

Hi guys,

I have the following scenario:

• Key account managers who visit their clients (accounts) during a period of time);
• The Key account managers can visit their accounts according to their panels as well as accounts that don't belong to their panels;
• The call's table and account's table are in the same fact table (concatenate) identified by a field called SOURCE;
• Everyday I store the panel's version for each manager and its identifier is the date.

CHALLENGE:

• I need to calculate a panel coverage that shows the ratio between the accounts that a manager visited (only accounts that belong to its panel) and the manager's panel;
• I have to consider always the last panel available according to the calendar selection criteria. Example:
  • If I select 2017-09, I'm going to consider the panel of 9/30/2017;
  • If I select 2017-07 and 2017-08, I'm going to consider the panel of 8/31/2017.
  • If I select Q1-2017, i'm going to consider the panel of 3/31/2017.

I have created a variable that already handles the item above (vSys.Account.MaxDate)

The formula below is kind of working, but it does not work when I use Period as dimension to check KPI evolution.

Formula rule:

• Numerator:
  • Intersection between the managers calls and the last panel available according to user calendar selection;
  • vACCOUNT_StartDate: This variable sets the date I started validating the panel (august 24, 2017);
  • KEY_PANEL: Its a key that contains country+employee name+account id. I'm using it because the accounts can be into different panels;
  • TimeFlag: It's just a calendar field that says if the period is actual (1), previous (0).

• Denominator:
  • Last panel available according to users calendar selection criteria.

avg({<date = {">=$(vACCOUNT_StartDate)"}>}

aggr(

        sum({<date = {">=$(vACCOUNT_StartDate)"}>}

        Aggr(

        Count({< SOURCE = {"CALL"}, TimeFlag = {1}, KEY_PANEL = P(KEY_PANEL), date = {">=$(vACCOUNT_StartDate)"}> *

            < KEY_PANEL = P({< SOURCE={'PANEL'}, date = {$(=$(vSys.Account.MaxDate))} >} KEY_PANEL), TimeFlag={1} >} distinct account)

        , Period, employee name))

        /

        Count({<KEY_PANEL = P({< SOURCE={'PANEL'}, date = {$(=$(vSys.Account.MaxDate))} >} KEY_PANEL), TimeFlag={1} >} distinct account)

,Period, employee name))

This formula seems to work fine, but only if I have some Period selected.

If I don't select any filter, the calculation is wrong. Moreover, If I try to create a chart using Period as dimension, the values are also wrong.

My question is:

• How could I pass the last panel available per Period in numerator and denominator using Aggr?
• Can you see syntax errors in the intersection and the use of P() function?
• Would you try a simpler way to calculate Panel Coverage?

Any insight form you guys would be very helpful.

I'm trying to create an app to demonstrate, but couldn't yet, since the data is restrict and the model very complex.

Thank you in Advance.

Rubens Rodrigues