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.
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)
From-ToDate.png 1.8 K
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
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,
- Load * Inline
- 'WidgetId', 'Widget Name', 'Widget Class', 'Date'
- 1, 'Dovakhlin Ice Widget', 'Ice', '08/21/2017'
- 2, 'Call of WidgiDuty', 'Video Game', '08/03/2017'
- 3, 'Widgimon', 'Water', '08/04/2017'
- 4, 'Widgimon1', 'Earth', '08/02/2017'
- 5, 'Widgimon2', 'Game', '08/02/2017'
- 6, 'Widgimon3', 'Vehicle', '08/08/2017'
- 7, 'Widgimon4', 'Car', '08/06/2017'
- 8, 'Widgimon5', 'Laptop', '08/05/2017'
- 9, 'Widgimon6', 'Mobile', '08/04/2017'