Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ashishpalkar
Creator III
Creator III

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
Specialist III
Specialist III

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

!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

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ashishpalkar
Creator III
Creator III
Author

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. ;
Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
ashishpalkar
Creator III
Creator III
Author

Yes that's correct.

JustinDallas
Specialist III
Specialist III

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