How to Tie Date Filter Pane to Dataset?

[BrianDH]

I have 2 filter Panes populated by a Calendar dataset.

LOAD
Date("Date", 'M/D/YY') AS AdmitDate,
"Day" AS AdmitDateDay,
FYr AS AdmitDateFiscalYear,
FMo AS AdmitDateFYMo,
YearMonth As AdmitYearMonth,
weekday(date("Date", 'MM/DD/YYYY')) As AdmitDayofWeek,
[FY Quarter] as AdmitFYQtr,
[CY Quarter] as AdmitCYQtr
FROM [lib://QVD Files/YearMoDay.qvd]
(qvd) WHERE "Date" >= YearStart(AddYears(today(),-3)) and "Date"<Today(1);

1 filter Pane is using (AdmitDateFiscalYear)

2nd filter pane (AdmitDateFYMo)

My main dataset date field is ENTERED_DTTM

V_CT:
LOAD ......
ENTERED_DTTM,
OPNAME
FROM [lib://QVD Files/PeopleSoft/V_CT.qvd]
(qvd);

How do I link these so when someone makes a selection from the filterpane it effects the display?

[Stoyan_Terziev]

Hello Brian,

I hope I understand correctly.

Seems the two tables: YearMoDay.qvd and V_CT.qvd both have a Date field which should serve as a key between the tables.

If so:

My recommendation is to first make sure they are set with the correct format which is done like:

LOAD
Date(Date#("Date", 'MM/DD/YYYY'), 'M/D/YY') AS AdmitDate,

...

where the first 'MM/DD/YYYY' should reflect the format of the data you are inputting.

and then in the second table:

V_CT:
LOAD ......
DATE(Date#(ENTERED_DTTM, 'MM/DD/YYYY'), 'M/D/YY') as AdmitDate,
OPNAME
FROM [lib://QVD Files/PeopleSoft/V_CT.qvd]
(qvd);

Just name them the same way and they will link.

Let me know if this helps!

Regards,

S.T.

[BrianDH]

Close but no cigar.  I must be doing something wrong.

AdmitDateCalendar:
LOAD
Date("Date", 'M/D/YY') AS AdmitDate,
Yr AS AdmitDateCY,
Mo AS AdmitDateCYMo,
"Day" AS AdmitDateDay,
FYr AS AdmitDateFiscalYear,
FMo AS AdmitDateFYMo,
YearMonth As AdmitYearMonth,
weekday(date("Date", 'MM/DD/YYYY')) As AdmitDayofWeek,
[FY Quarter] as AdmitFYQtr,
[CY Quarter] as AdmitCYQtr,
Date("Date", 'yyyy-MM-dd') AS ENTERED_DTTM
FROM [lib://QVD Files/YearMoDay.qvd]

V_CT:
LOAD ....
//ENTERED_DTTM,
Date(ENTERED_DTTM, 'yyyy-MM-dd') AS ENTERED_DTTM,
OPNAME
//(MONTH(DATE(ENTERED_DTTM)) &' - '& YEAR(DATE(ENTERED_DTTM))) as %FYMo,
//(YEAR(DATE(ENTERED_DTTM))) as %FYYear
FROM [lib://QVD Files/PeopleSoft/V_CT.qvd]

[Stoyan_Terziev · Accepted solution]

Hi Brian,

You need to use both those functions Date() and Date#() to get it resolved.

If one table gives you this date format: 15-09-2019 you need to use

Date(Date#([DateField], 'DD-MM-YYYY')) as AdmitDate

If the other table gives you a date format which looks like this: 9/15/2019 you need to use 

Date(Date#([DateField], 'M/D/YYYY')) as AdmitDate

Replicating both functions may for the two sources may not work for you if they have different input formats.

Kind regards,

S.T.

[BrianDH]

Within the filter pane do I need something like

= if(AdmitDateFiscalYear = date(year(entered_dttm))

[BrianDH]

Got it!!

LOAD
Date("Date", 'M/D/YY') AS ENTERED_DTTM,
Yr AS EnteredDateCY,
Mo AS EnteredDateCYMo,
"Day" AS EnteredDateDay,
FYr AS EnteredDateFiscalYear,
FMo AS EnteredDateFYMo,
YearMonth As EnteredYearMonth,
weekday(date("Date", 'MM/DD/YYYY')) As EnteredDayofWeek,
[FY Quarter] as EnteredFYQtr,
[CY Quarter] as EnteredCYQtr
FROM [lib://QVD Files/YearMoDay.qvd](qvd)
WHERE "Date" >= YearStart(AddYears(today(),-3)) and "Date"<Today(1);

V_CT:
LOAD  ......
Date(floor(ENTERED_DTTM),'MM/dd/yyyy') as ENTERED_DTTM,
OPNAME
FROM [lib://QVD Files/PeopleSoft/V_CT.qvd]
(qvd);

Thanks Very Much

[SoftUser]

I had an issue with the filter pane data formating and I used the following query. That worked

=DATE([BUSINESS_DATE],'DD/MMM/YYYY')