14 Replies Latest reply: Feb 24, 2017 10:56 AM by Aman Jain

# 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).

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

Dimension : Year(Supply Date field)

Measure: Count ( Supply Date field)

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.

• ###### Re: combine two dates field on one chart

Create a calendar date and load like this

Calendar:

Date

From

Master Calendar;

Supply:

[Supply Date],

[Supply Date] as Date,

1 as [Supply Count]

From

Supply;

Demand:

[Demand Date],

[Supply Date] as Date,

1 as [Demand Count]

From

Demand;

Give dimension as Year(Date) and measures as sum([Supply Count]) and sum([Demand Count]) in a Line Chart

• ###### Re: combine two dates field on one chart

Thanks Aar kay for the reply

But I dont have any master Calender.

How should i create a calender Date?

• ###### Re: combine two dates field on one chart

Sample to create calendar dates

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

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

LET vToday      = num(Today());

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

Calendar:

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

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

AUTOGENERATE

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

• ###### Re: combine two dates field on one chart

So did you call this section in the script as Master calender??

When i try to do this in the script it did not identify the Master calender

• ###### Re: combine two dates field on one chart

I am getting error here. The date and master calender seems like is not identified.

• ###### Re: combine two dates field on one chart

I just gave you a sample code aman, try creating a calendar and load and link that date field  to both the supply and demand dates that way there will be a common link to show the comparison on a graph.

Can you share the sample file

• ###### Re: combine two dates field on one chart

So here is the attached sample.

Consider Actual PR1 as Demand

and Actual PR3 as Supply.

Go to the Data script and you will see the error.

Let me know if you need anything else.

• ###### Re: combine two dates field on one chart

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:

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

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

AUTOGENERATE

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

Projects:

"RPM #",

"Actual PR1",

"Actual PR3"

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

(ooxml, embedded labels, table is Project);

Supply:

RPM#,

[Actual PR3]

Resident

Projects:

Demand:

RPM#,

[Actual PR3]

Resident

Projects:

Drop Table Projects;

• ###### Re: combine two dates field on one chart

Load your data into a fact table that combines the two measures

Commodity

Fact Type -> supply / demand

Date

Value

Should let you compare your data how you want.

• ###### Re: combine two dates field on one chart

But still i dont understand whats a fact table.

How to create it ??

At the end i need a line chart showing the values.

• ###### Re: combine two dates field on one chart

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.

• ###### Re: combine two dates field on one chart

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

• ###### Re: combine two dates field on one chart

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

• ###### Re: combine two dates field on one chart

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.