Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
BrianDH
Creator II
Creator II

How to Tie Date Filter Pane to Dataset?

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?

Labels (1)
1 Solution

Accepted Solutions
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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.

View solution in original post

6 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II
Author

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
Partner - Creator III
Partner - Creator III

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
Creator II
Creator II
Author

Within the filter pane do I need something like

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


BrianDH
Creator II
Creator II
Author

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
Contributor II
Contributor II

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

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