Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi There
I have requirement to create Start Data and End date from same date field from database.
Upon selecting date range in app , data should be display based on selected date range.
Can you please suggest how to do it in Qlik Sense.
Thanks in advance,
You would probably need to have a MasterCalendar with a Join table between the Calendar and your Fact table. This would probably also require that your join table be a RolePlaying table too. Here is an example script.
WidgetProductionFact:
Load * Inline
[
'WidgetId', 'Widget Name', 'Widget Class', 'Start Date', 'End Date',
1, 'Dovakhlin Ice Widget', 'Ice', '08/21/2017','09/20/2017',
2, 'Call of WidgiDuty', 'Video Game', '08/03/2017','12/20/2017',
3, 'Widgimon', 'Video Game', '08/03/2017','12/20/2017'
]
;
WidgetProductionDateRPTable:
LOAD WidgetId, [Start Date] AS %widgetDate, 'Start Date' AS RoleType Resident WidgetProductionFact
;
LOAD WidgetId, [End Date] AS %widgetDate, 'End Date' AS RoleType Resident WidgetProductionFact
;
DROP FIELDS [Start Date], [End Date] FROM WidgetProductionFact
;
MasterCalendar:
Load
TempDate AS '%widgetDate',
DayStart(TempDate) as CalDate,
Week(TempDate) As Week,
Year(TempDate) As Year,
Month(TempDate) As Month,
Day(TempDate) As Day,
'Q' & ceil(month(TempDate) / 3) AS Quarter,
Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay
;
//=== Generate a temp table of dates ===
LOAD
date(mindate + IterNo()) AS TempDate
,maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
min(FieldValue('%widgetDate', recno()))-1 as mindate,
max(FieldValue('%widgetDate', recno())) as maxdate
AUTOGENERATE FieldValueCount('%widgetDate');
EXIT Script
;
You would then provide the user with the ability to select on the properties of the Calendar table.
!Hi Justin
Thanks for the detail info its really helpful.
However our users are specifically looking for data between 2 dates. Do i have to make any changes in above mentioned script to achieve this? (please find attached screenshot)
Thanks again
How you are going to work? Let's take condition like if we have one date field called "Date" and then how you need to use from this Date field to Start and End dates. Can you confirm once your intention is here.
What you shown in image those are simple objects, But qliksense doesn't have this future we need to set up Extension for that
Hi Anil
Thanks for your reply, I have send image as an example.
In above example showed by Justin , we are receiving only 1 date field from Database. And we want to construct Start Date and End date from the given Date field.
In the Qlik Sense app user will select the Start Date and End Data and accordingly data will be displayed.
Hope this helps,
That means, You are simply using same field to End field.
Yes that's correct.
Whoops, I totally misread your question. Now that I've read some of your comments, I'm not sure how to make a field searchable range-wise i.e 'Show me all baseball games that have FirstPitchDate between a StartDate and EndDate".