Double dates

[jacob2102]

Hi,

I have a customer's table that includs different dates. These dates refer to the customer's registration and withdrawal.

For the report, I need to create different KPI's to obtain anual values, changing the year with data segmentation.

What date do I have to use to know the customers's registrations and withdrawal in the selected year? At this moment, If I use one of this dates the resoult is filtered with both dates. On the other hand, I try create a date table, independent to customer table, but I need creat a relationship between the tables and I obtain the same error if the relation is through one of this dates.

Do you know how can I solve it?

Thanks in advance.

[Mariusz]

Hi @jacob2102 

Can you provide a data sample + expected outcome?

Best Regards,
Mariusz

Please feel free to connect with me.

[jacob2102]

Hi @Mariusz ,

This is an example:

Account:

Account_Id	Name	CreateOn	CloseOn
A1	Account1	23/05/2008	14/10/2010
A2	Account2	15/06/2010
A3	Account3	21/01/2010	15/12/2010
A4	Account4	13/08/2010
A5	Account5	15/10/2019

Contact:

Contact_Id	Name	Email	Account_Id
C1	Contact1	contact1@contact.com	A1
C2	Contact2	contact2@contact.com	A2
C3	Contact3	contact3@contact.com	A3
C4	Contact4	contact4@contact.com	A4
C5	Contact5	contact5@contact.com	A5
C6	Contact6	contact6@contact.com	A3
C7	Contact7	contact7@contact.com	A4
C8	Contact8	contact8@contact.com	A2
C9	Contact9	contact9@contact.com	A5

For the expected outcome, I want to create a data segmentation with a date and when I filter for any year of the selector get a result for the customer registrations and withdrawals of the year. I want to provide two cards in the report, one of them for customers registrations and the other for customer withdrawals.

For example, If I select "year 2010" in data segmentation, I want to obtain "3" for customers registrations and "2" for customer withdrawals.

I hope that this example let you understand the situation. 

Best regards.

Jacob

[Mariusz · Accepted solution]

Hi @jacob2102 

Please see the attached file with the solution.

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.

[jacob2102]

Hi @Mariusz ,

Thanks so much for your answer and solution.

Now I can display both KPI in the same report.

Best regards,

Jacob