Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello! I've been assigned to create filters for our sheets for our Fiscal Year (July-June). We already have a table (SD_DATES) with every date within our date range, as well as fields breaking out the date and assigning it to the correct fiscal year.
I have two additional tables that contain the data we are using (SD_WR) and (SD_WR_OTHER). However, both have their own date fields:
SD_WR has two date fields representing the date a work order was started (DATE_REQUESTED) and the date a work order was completed (DATE_COMPLETED).
SD_WR_OTHER has one date field representative of materials purchased on a specific date (DATE_USED).
All are formatted the same was (mm/dd/yyyy).
My supervisor is wondering if the (SD_Dates) can be an unbound filter (like in Access), which can allow for filtering of all the data on a sheet to the selected year. month, etc., regardless of whether that chart is referencing a specific date field (DATE_REQUESTED, DATE_COMPLETED, or DATE_USED).
Eventually we will be adding tables with more date fields, and tables with more than one date field, and he isn't interesting in creating dates tables to associate to every single date field.
Is something like this possible via a script or an expression? If further explanation is needed, I can try explaining better. Thanks for any help!
You will want to connect the date fields to the SD_DATES table using a link table. Download the sense example here:
Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
for a tutorial.
-Rob
Hi Casey,
Look in this direction, may be this help you
Adding alternative dimensions and measures ‒ Qlik Sense
Re: How to use alternate state?
Regards,
Andrey
I'm not familiar with Altered States as I just started working with QlikSense. It seems like it would require "States" boxes to switch between states, which might contain different date data. It also seems like its better for comparison. We don't want numerous charts and graphs to compare different date fields, we essentially just want to filter by out SD.DATES table fields and have it apply to all charts using the other date fields from different tables.
You will want to connect the date fields to the SD_DATES table using a link table. Download the sense example here:
Qlikview Cookbook: Tutorial - Using Common Date Dimensions http://qlikviewcookbook.com/recipes/download-info/tutorial-using-common-date-dimensions/
for a tutorial.
-Rob
Thank you!
One final question, so that tutorial worked perfectly for creating a DateLink box and joining my Date fields to it. However. it then shows me how to connect to a generated calender script.
I don't need that as I already have my "WR_DATES" table with every date listed in my time frame. How do I link the generated 'Date" field in the DateLink box to my "WR_DATES" table and the field "DATES" within it?
You won't need to generate a calendar table as you already have a calendar table. So you can link directly as I think you already did:
SD_WR as SD_DATE
SD_WR_OTHER as SD_DATE
-Rob
What would the correct syntax be for scripting that in the load editor?
Somthing like:
DateLink:
LOAD KeyField,
SD_WR as SD_DATE,
'WR' as DateType
Resident SD_WR;
-Rob
Thank you!