using differents calendars for the same chart

[#1]

hello,

I would like your opinion and advice for using calendar in chart.

Let me explain you by example what i want to do.

The first table contains the list of the interactions with ID, open date, close date and priority fields.

One interaction has several statuts during it's life, which are in the second table : ID, begin date, close date and statut.

For each date, I create the differents formats like this :

interaction:

LOAD Id,
     [Open date],
     date(monthstart([Open date]), 'MMM-YYYY') AS MoisAnnée_Open_date,
     [Close date],
     date(monthstart([Close date]), 'MMM-YYYY') AS MoisAnnée_Close_date,
     priority
FROM
testcal.xlsx
(ooxml, embedded labels, table is interaction);

statuts:
LOAD Id,
     [Begin date],
     date(monthstart([Begin date]), 'MMM-YYYY') AS MoisAnnée_Begin_date,
     [End date],
     date(monthstart([End date]), 'MMM-YYYY') AS MoisAnnée_End_date,
     Statut
FROM
testcal.xlsx
(ooxml, embedded labels, table is statuts);

Now, I have all data i need, I want to create chart in order to have theses views :

The chart with the number of opened interaction per month is ok (using MoisAnnée_Open_date for the dimensions) :

I can do the same with the closed interaction  (using MoisAnnée_Close_date for the dimensions)

.

But if i want in the same chart the number of opened and closed interaction per month. Something like that :

The first one (orange) is the number of interaction opened by month, and the second one (grey) is the number of closed interaction per month.

One interaction coulb be opened in one month and closed in a other month.

So if i Use one date format for dimensions (MoisAnnée_Open_date OR MoisAnnée_Close_date.), the same interaction appears in the same month for open and close case, even if this interaction was closed in a other month.

After that, I want to make chart in the same way with the date of open/close interaction and also with the date of begin/end of every statuts.

For each month, how many opened and closed interaction, how many statuts 'affected' has begun and ended , the same for every statuts...

Is anyone had this problem and has found a solution?

Thank you

Stéphane

[#2 · Accepted solution]

I've found the solution

First, iI created a  master calendar, and after that I made link between dates of my data and the master calendar like this :

So, I created chart with special condition in order to see the data in the date reference i want : Example from the attached qvw :

=  count (distinct  if( ID=requete_create, ID))

We have now the chart like this :

Don't hesitate to ask me more questions if this explanation isn't clear for you.

stéphane