3 Replies Latest reply: May 1, 2016 5:18 PM by Stefan Wühl RSS

    Extreme date manipulations

    Chiwa1 Chiwa1

      Hello everyone!

       

      I'm very to new to Qlikview, coming from legacy IT and BI systems.

      looking forward to see the community in action.

      I spent sometime researching the forum and the web but couldn't the info I was looking for.

      a robust, agile and reusable solution to manipulate sales data by two calendars, date ranges and actual work day.

       

      This is going to be rather long, bear with me if you can.

       

      The main table:

      1. Sales data - basically -> SalesDate | SalesPrice | ProductCode


      Here is the situation, I would like to have an extremely flexible and easy way to filter sales data as follows.

       

      I want to be able to change instantly between two calendars (Regular and Company), and to combine traditional periods such as quarter and months with date ranges.

       

      For each date I store in a master calendar table two rows, to list if it's a working day (value between 0 and 1) and to which periods it belongs, here's an example.

      note that a date can have a completely different periods in the company calendar.

        

      Calendar TypeDateYearQuarterMonthWorkday
      Regular30-12-1520154120
      Company30-12-1520154120
      Regular31-12-1520154121
      Company31-12-152016111

       

      The ideal user experience as I see it:

      At the top of the sheet the user will select

      1. Select calendar: Company/Regular

      2. Start Date:(DD-MM): 01-01

      3. End-Date:(DD-MM): 31-12

      4. Select comparison: dates/workday


      After selections are made and every time they change the Sales data field will filter accordingly: (leaving only relevant rows from sales data)

      My thinking for solution is:

      1. Write a function/formula like this:

      IsInRange(SalesDate,CalendarType,StartDate,EndDate,Workday (Y/N) )


      so four kind of checks are required:

      1. if it's a regular calendar and not by workdays. just check if date is between the range for that year

      2. if it's a regular calendar and by workdays. check the sum of total work days in year from year start until StartDate and up until EndDate, and check if the total of working days from year start to SalesDate is in between.

      3.The same as 1 but with Company calendar

      4. the same as 2 but with company calendar


      2. sum the sales in all charts with the formula SUM(IsINRange(SalesDate)= True, SalesPrice)

      3. create trigger for all the var listed above for data refresh


      My questions:

      1. is that a good solution in your opinion, would you do it differently ?

      2. should I worry about performance and consider losing flexibility by calculating YTD flags in load instead?, sales data contains 1 Million rows.

      3. How do I write a formula like this, can you please refer me in the right direction?

      4. How do I force a re-calculation for every change


      Thanks for reading, I would love to hear your comments.



        • Re: Extreme date manipulations
          Stefan Wühl

          Chiwa1,

           

          if I understood your post correctly, what you are asking for should be quite usual and straight forward in QlikView / QS.

           

          I am not sure how much experience you have with Qlik data models and how Qlik's associative data model works.

           

          I would suggest that you create a master calendar with all dates needed just as you described above. This calendar should link to the sales fact table by Date field.

           

          Then all you need to do is: make selections

           

          a) Create a list box for Calendar Type and select a value, then open properties and enable 'always one selected value', to make sure that only one calendar is used at any time.

           

          b) If you want, create more list boxes for Date, Month, Year,, etc.

          You can select ranges e.g. by using searches:

          The Search String

           

          Or by using calendar objects or variables and maybe using triggers to use the variables for date range selections.

          There are lot of examples here in the forum on how to do that.

           

          In the list box for Workday, either select 1, or clear the selection to have both values active in the record set.

           

          Create charts with calendar fields as dimension and expressions to aggregate your sales facts as needed.

           

          You should already be set up to do all analysis you required.

           

          You can automate some reports by using set analysis, i.e. defining 'static' filters in your charts.

           

           

          If you have not started with QlikView or QlikSense, I would suggest that you go though some basic tutorial (available for free on the Qlik site), You should be ready to go within some hours / few days.

           

          Hope this helps,

          Stefan

            • Re: Extreme date manipulations
              Chiwa1 Chiwa1

              Hi,

               

              Thanks for the tips!

              I managed to do some of the stuff I wrote, by creating a YTD flag in the load script.

              The only thing I want to achieve but still haven't figured out how is to create a Year to date total work days for each date in the calendar

               

              I'm loading all the columns and want to calculate the Year to Date - SUm Work Days column during the load script.

              Please note the Year, Month and Day are loaded for each row and varies depending on the calendar.

               

              Totally lost in achieving this,


              Thanks

               

                

              Calendar TypeDateYearQuarterMonthDayWorkdayYear to Date - SUM -Work Days
              Regular30/12/20152015412300NA
              Company30/12/20152015412320NA
              Regular31/12/20152015412311NA
              Company31/12/2015201611111
              Regular01/01/2016201511111
              Company01/01/2016201611212