Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ashishpalkar
Contributor II

Create Start date / end date from same date field

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, 

7 Replies
JustinDallas
Valued Contributor II

Re: Create Start date / end date from same date field

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.

ashishpalkar
Contributor II

Re: Create Start date / end date from same date field

!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

Re: Create Start date / end date from same date field

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

Life is so rich, and we need to respect to the life !!!
ashishpalkar
Contributor II

Re: Create Start date / end date from same date field

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,


  1. WidgetProductionFact: 
  2. Load * Inline 
  3. 'WidgetId', 'Widget Name', 'Widget Class', 'Date'
  4.     1, 'Dovakhlin Ice Widget', 'Ice', '08/21/2017'
  5.     2, 'Call of WidgiDuty', 'Video Game', '08/03/2017'
  6.     3, 'Widgimon', 'Water', '08/04/2017'
  7.     4, 'Widgimon1', 'Earth', '08/02/2017'
  8.     5, 'Widgimon2', 'Game', '08/02/2017'
  9.     6, 'Widgimon3', 'Vehicle', '08/08/2017'
  10.     7, 'Widgimon4', 'Car', '08/06/2017'
  11.     8, 'Widgimon5', 'Laptop', '08/05/2017'
  12.     9, 'Widgimon6', 'Mobile', '08/04/2017'
  13. ;

Re: Create Start date / end date from same date field

That means, You are simply using same field to End field.

Life is so rich, and we need to respect to the life !!!
ashishpalkar
Contributor II

Re: Create Start date / end date from same date field

Yes that's correct.

JustinDallas
Valued Contributor II

Re: Create Start date / end date from same date field

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".

Community Browser