Can two time dimensions be on the same x axis?

[#1]

Hello,

I am trying to show positive and negative integers for each month as per the below chart.

However, I have two separate time dimensions in my data. The way the data exists in my database is:

• [Gift Date] - which I want in the master calendar in all cases
• [Gift Status Date] - which I want when the [Gift Status] is Cancelled (all records will have a status date but unless it's cancelled I want to disregard it

So for some entries in my database it should pull through two dates, and for some only one (dependent on the status).

Currently I am copying two extracts into Excel in a simplified format depending on Gift Status, but I'd like to link it to SQL, hence the need to work around the two different fields.

I'm having to load the two dates using different field names- what is the best way to either link both to the master calendar (so that there is just one time axis on the chart) or otherwise get Qlikview to recognise them as the same thing? Basically I want to see:

• in June 2016 (based on Gift Date) what is the count of ID
• in June 2016 (based on Gift Status Date) what is the count of ID where the status is Cancelled (*-1 so it is negative)

And have both against June 2016 on the x axis. The set analysis I'm fine with, I just can't find any way to link both date fields together as a single dimension.

Any help appreciated, and if there's anything I can do to clarify the problem please let me know!

Thanks in advance,

Hannah

[swuehl · Accepted solution]

Hi Hannah,

I think you can use a canonical date for this:

Canonical Date

Tutorial - Using Common Date Dimensions and Shared Calendars

[#3]

Thank you so much Stefan, I have spent a frustrating few hours trying to figure that out!