Multiple Date, but need one filter based on all Dates

[ljames88]

I have an issue where I need to be able to have multiple date, Order Date, Ship Date, Payment Date. 

In my example, I have an Order that has an Order Date in one table, in addition a ship date in another table, and a payment date in another table.  I need to link all tables by order number.  However I need to be able to have a "Transaction Date" so I can see what happened in a particular month.

For example, Order #123 has an Order Date of 07/31/2017, ShipDate of 08/01/2017, and a PaymentDate of 08/01/2017.

Order #789 has an order date of 07/30/2017, ShipDate of 08/01/2017, and Payment Date of 07/31/2017.

I need to be able to both filter the report on each date, which I am able to do with not problem, but I also want to create a "Transaction Date", that I can filter on.  So if I create a "Transaction Date", and say show me everything that happened in the month of August,  It would should be the Order#123 because it has transactions (SHIPDATE and PAYMENTDATE) and it would show me Order#789 because it also has transactions (ShipDate).

I can't seem to do this without it creating a loop.

Thoughts?

[#2]

Hi Lance,

Your best bet is to create a link table to join all three tables.

Your link table will have following:-

Order No

Date

Combination of Order No and Order Date as a key to connect with Orders table

Combination of Order No and Ship Date as a key to connect with Shipping table

Combination of Order No and Payment Date as a key to connect with Payments table

Your individual tables will have the relevant key and not the Order No.

Hope this helps.

Thanks & Best Regards,

Kuldeep Tak

[dinicholls]

Hi,

Have you thought of using a canonical date bridge?

Its basically a master calendar for your dates! I can just select one date, and it will show everything that was opened and closed on that date.

This is mine:

Date Bridge:

Load

query_id,

query_start_date as CanonicalDate,

'QueryStartDate' as DateType

Resident Queries;

Load

query_id,

query_date_last_changed as CanonicalDate,

'QueryEndDate' as DateType

Resident Queries;

CanonicalTempMinMax:

Load

Min(CanonicalDate) as CanonicalMinDate,

Max(CanonicalDate) as CanonicalMaxDate

Resident DateBridge;

LET vCanonicalMinDate = Peek('CanonicalMinDate',0,'CanonicalTempMinMax');

LET vCanonicalMaxDate = Peek('CanonicalMaxDate',0,'CanonicalTempMinMax');

LET vCanonicalToday = $(vCanonicalMaxDate);

//***************************************Temp Calendar

CanonicalTempCal:

Load

date($(vCanonicalMinDate) + rowno()-1) as CanonicalTempDate

AutoGenerate

$(vCanonicalMaxDate) - $(vCanonicalMinDate)+1;

Drop Table CanonicalTempMinMax;

//**************************************Master Calendar

Canonical_MasterCalendar:

LOAD

Floor(CanonicalTempDate) As CanonicalDate,

Week(CanonicalTempDate) As Canonical_Week,

Year(CanonicalTempDate) As Canonical_Year,

Month(CanonicalTempDate) As Canonical_Month,

Day(CanonicalTempDate) As Canonical_Day,

Weekday(CanonicalTempDate) As Canonical_Weekday,

'Q'&ceil(month(CanonicalTempDate)/3) As Canonical_Quarter,

Date(monthstart(CanonicalTempDate), 'MMM-YYYY') As Canonical_MonthYear,

Week(CanonicalTempDate)&'-'&Year(CanonicalTempDate) As Canonical_WeekYear,

inyeartodate(CanonicalTempDate,$(vCanonicalToday),0)*-1 As Canonical_CurYTDFlag,

inyeartodate(CanonicalTempDate,$(vCanonicalToday),-1)*-1 As Canonical_LastYTDFlag,

inmonthtodate(CanonicalTempDate,$(vCanonicalToday),0)*-1 As Canonical_CurMTDFlag,

inmonthtodate(CanonicalTempDate,$(vCanonicalToday),-1)*-1 As Canonical_LastMTDFlag

Resident CanonicalTempCal

Order By CanonicalTempDate ASC;

Drop Table CanonicalTempCal;