Exclude one dimension from a calculation

[NicoEtourneau]

Hello everyone,

I am strugling with a calculation in a table. This table shows the performance of a team visiting doctors. These doctors can have a qualification (Categoria LRP : A, AA or AAA) but not all of them have one of these qualification.

This table has 2 dimensions : The category of the doctors and the person from the team. 

My goal is to display the number of field days for each person of my team (the number is different for each of them).

I tried using this formula :

=Count({< IdActivityType={$(=Activities_Field)}, ActivityDate={">=$(=date(min({[cycle]}FromDate)))<=$(=date(max({[cycle]}ToDate)))"}>} total <ParentIdHierarch> distinct IdActivity)

It gives me the number of field days but only if there are activities with Categoria LRP doctors. I want to have all field days, even those with other doctors.

I tried using :

=Count({< IdActivityType={$(=Activities_Field)}, CategoriaLRP=, ActivityDate={">=$(=date(min({[cycle]}FromDate)))<=$(=date(max({[cycle]}ToDate)))"}>} total <ParentIdHierarch> distinct IdActivity)

but it did not work.

Do you have ideas to ignore the dimension ?

Thanks in advance

Nicolas

[NicoEtourneau]

Just a precision :

If I remove <ParentIdHierarch> from the total, the total is correct (It doesnt take only Categoria LRP doctors activities) but it is the sum of field days of all members of the team (the number displayed is the same for all users).  What I need is a different total of field days for each member.

Thanks Nicolas

[agigliotti]

which are the chart dimensions are you using?

[NicoEtourneau]

Hi,

The data comes from a CRM software. Users can indicate their daily activities and link them to the doctors they visit.

The 2 dimensions are :

- ParentIdHierarch : If I am connected as a manager, It shows all the members of my team

- CategoriaLRP : It is a Potential of doctors that users link to the visit (All doctors do not have a value of CategoriaLRP)

What I need is the number of field days for each user (the value is different for all of them). Right now the only thing I managed to have is the number of field days for each users but only the field days with a CategoriaLRP doctor. I cannot ignore the CategoriaLRP dimension.

Thanks for your help

[agigliotti]

could you share the qvw document in order to understand better what's your actual and expected result ?

[NicoEtourneau · Accepted solution]

Hi, 

I found another way to do what I wanted to do using aggr function.

Thanks for your time

Nicolas