Use graph dimension in a measure formula

[ClementByr]

Hello,

I am currently trying to use a graph dimension to make condition inside a measure.

Here is some data for testing :

Machine table

Machine	Category	Creation Date
P2XQ5NSIAQ	type01	03/05/2013
CT4P9LIHCT	type01	16/08/2013
BLJT3F32S1	type01	17/01/2014
LW26RJG4AN	type01	29/11/2015
7CC3HFWQ5K	type01	11/01/2019
V2UFGK759O	type01	09/03/2019
898JG2HJS2	type01	25/06/2019
46NHQOIGA9	type02	02/08/2013
RHYQHJS3Y4	type02	15/02/2019
GNA1XO9P6S	type02	21/03/2019
6XYD3QXROZ	type02	28/03/2019
YQKQLVKNIO	type03	22/04/2013
7MR5HSHK4B	type03	24/01/2019
VKLH70YTPB	type03	04/02/2019
DS9B1TK0ZX	type04	13/12/2013
SQJW7SS8AY	type04	07/04/2014
FJ5BL5DPFG	type04	25/11/2015
NSDKXCJEY6	type04	09/09/2018
I2D0NBEETX	type04	10/03/2019
A4P19TZXQ5	type04	26/07/2019

Tranz table

Date_tranz

janv-19

févr-19

mars-19

I need to count how many machine I already have for each categories by Date_tranz. So I need to to put the dimension Date_tranz and do a measure like this : count({<category={'type01'}>} Machine).</category={'type01'}>

This measure count all the machine and I want to exclude machines that have Creation Date more recent than Date_tranz, since these machines does not exist at that time.

Does anyone know if it is possible, please ?

Best regards.

[JordyWegman]

Hi,

Can you show us your Data Model Viewer? Is there a link between Machine Table and Tranz Table?

Jordy

Climber

[ClementByr]

Hello Jordy,

The data between the Machine Table and Tranz Table are not related, the Tranz table is just a table with a period of months that I have chosen.

I am trying to compare Creation Date from the Machine Table with the Date tranz to check if the Creation Date of a machine is older than the Date tranz to be sure that the machined existed at this date. I wish, if possible, to add this condition on top of the count that I have mentionned before : count({<category={'type01'}>} Machine).

Thanks,

Clément.

[JordyWegman]

Hi Clément,

Check the attachment for the application. I've added a column with MonthYear, your Date tranz. Resulting in:

Jordy

Climber

[ClementByr]

Hello Jordy,

Thanks you but this is not the result I am looking for. I am supposed to have the dimension Date_tranz to know the quantity of machines by types during the period defined in Date_tranz. The bar chart should also be cumulative because the machine parc increase following the Creation Date.

Regards.

Clément.

[JordyWegman · Accepted solution]

That is no problem, you can create another table with Date_tranz and a key to MonthYear. This way you can make your own period.

About the bar chart, check the following attach example, is this what you want?

Jordy

Climber

[ClementByr]

Hello Jordy,

Thank you Jordy, that's exactly what I wanted.

I am gonna add a filter on the Creation Date to show only the data on the period in the Date_tranz.

Best regards,

Clément