Linking Dates in a Visual

[KatieH]

Hi all,

Looking for a bit of help with this one as I'm not even sure whether it can be done!

I have 2 tables in my report, 1 showing all registered users and 1 showing all quotes done on the system and by whom. These are linked on the User ID.

I want to see the correlation between users being registered and quotes being done. I would therefore like to see a line graph (or similar) showing how many users registered in a certain timeframe vs how many quotes were done within the same timeframe.

How do I set up the dates so that if I select July 2017 for example, it will show me the number of users with a creation date of 07/17 from table 1 AND, on a separate line, the number of quotes with a quote date of 07/2017 from table 2?

Is this possible?

Thanks,

Catherine.

[KatieH]

Hi @Greg_Deckler and @parry2k,

I have attached the diagram below. The Quote Data Extract and All Reg Users are already linked on the User ID. It will allow me to link the date to one of the table but when I try to do the other one, I get the message on the right.

[popov]

Hello, @KatieH

Do you still need help for this case?

[KatieH]

Really sorry popov - I hadn't seen your response.

I do still need help on this. I haven't found a solution yet.

Thanks.

[KatieH]

Thank you @Greg_Deckler and @parry2k,

I did try to link a calendar table to both tables but because the two are already linked, it would only allow me to link one of them because of that existing relationship.

Is there any way around this?

[Greg_Deckler]

You can try adjusting your relationship filter direction to allow you to create the relationship. I'd need to see a picture of your relationship diagram and understand why the tables are already linked and what they are linked on.

[parry2k]

i don't think you need existing relationship based on what you described, remove that relationship and try again.

Although can you share how your relationship looks like?

[Greg_Deckler]

Probably should create a central Calendar table and link it to both of your fact tables and then use the Calendar table date in your visuals.

[parry2k]

@KatieHyes it can be done, you need date dimension in your data model which is easy to create and then join your table on date with date dimension, use date from date dimension on x axis and values from tables.

here is the link on how to create table.