Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
amanjain57
Creator II
Creator II

combine two dates field on one chart

Hi all,

I have a case where demand is seen for last 4 years and also the supply.

Demand or supply may vary for a particular day. There will be days where there is no demand or supply, or it could be that there is demand (no supply) /supply (no demand).

Error2.1.JPG

Like here we see from years (2014-2017) how a commodity is.

Dimension : Year(Supply Date field)

Measure: Count ( Supply Date field)

error 2.2.JPG

And here is the demand for the same commodity in last 4 years.

Dimension : Year(Demand Date field)

Measure: Count ( Demand Date field)

I want to combine this two charts to show on a common chart so that i can compare them one to other.

What would be the best way to do it ??

Let me know the best thoughts you have around this.

14 Replies
aarkay29
Specialist
Specialist

LET vStartDate  = num(Floor(YearStart(Today(),-10)));   //for min Date 1/1/2002

LET vEndDate    = num(Floor(YearEnd(Today(),10)));    // //for max Date 12/31/2032

LET vToday      = num(Today());

LET vYesterday  = num(Today()-1);

Calendar:

LOAD

  $(vStartDate) + RowNo() - 1       AS Num,

  date($(vStartDate) + RowNo() - 1) AS Date

AUTOGENERATE

  $(vEndDate) - $(vStartDate) + 1;

// Load Data from Projects

Projects:

LOAD

         "RPM #",

         "Actual PR1",

        "Actual PR3"

   

FROM [lib://TIS Management Files (molex_ajain02)/Projects.xlsx]

(ooxml, embedded labels, table is Project);

Supply:

Load

     RPM#,

     [Actual PR3]

Resident

     Projects:

Demand:

Load

     RPM#,

     [Actual PR3]

Resident

     Projects:

Drop Table Projects;

ogster1974
Partner - Master II
Partner - Master II

If you are using the latest version of sense when you load in the fact table the first time it will auto generate a master calendar on your date for you.

amanjain57
Creator II
Creator II
Author

Andy can you send me a demo app.

or can you work on the Test(Work) App i uploaded earlier in the chat and show it to me??

Consider Actual PR1 as the Demand Date and Actual PR3 as the Supply date

amanjain57
Creator II
Creator II
Author

Andy ..Here is the attached file for your reference.

amanjain57
Creator II
Creator II
Author

Hi everyone !!

I found an excellent way to solve this problem

Refer to : Canonical Date

Linking to two or more dates

This solved my problem.