7 Replies Latest reply: Sep 1, 2011 12:11 PM by John Witherspoon RSS

    List Boxes for Dates / Days / Weeks / Quarters / Years

      All,

       

      What is the best way to provide multiple list boxes based on dates for data analysis?  Say I am reading in a date, but would like to provide list boxes based on:

       

      1.     The date itself;

      2.     Day of Week;

      3.     Week of Year;

      4.     Month;

      5.     Quarter;

      6.     Year.

       

      Should I read in the date multiple times in the script and manipulate it there so that each record in the various tables has these as dimensions?  Or can I justr read in the date and create list boxes by using a calculated dimension?

       

      I tried to do this for months using the following:

       

      =aggr(DISTINCT MonthName([Storage_Details Actual PROCESSED_DATE]),[Storage_Details Actual PROCESSED_DATE])

       

      This shows <MMM YYYY> as expected, but for some reason it is duplicated for each month.  Is there any way to just show <MMM>?

       

      Thanks

      Ian

       

        • Re: List Boxes for Dates / Days / Weeks / Quarters / Years

          Okay,

           

          I would really appreciate the community's input here - one on the solution offered by myself and its viability and two on the data modelling issue . . . as I am clearly not getting my head around it!

           

          Thinking through my own question above, I came to the conclusion that trying to extract the relevant information from a date and timestamp using QV would prove to be useless, as the underlying data would still be a date and timestamp (and it is only the presentation that is changed), and hence would not function as expected as a field selector - click on and select a specific "Month" to reduce the dataset to only that "Month".

           

          No problem, I felt that an elegant solution may be to create an Excel file with the date and the desired selector fields calculated based on the date.  I could then link the different tables that imported a date to the Master_Date table and viola, I would have universal access to the selector fields through the key to the Master_Date table.

           

          Alas, this did not work as planned and I got an error to say that there were loops in the data model.  I have attached this as a QVW file with no data.

           

          So. firstly is this idea of a Master_Date and related selector fields appropriate?  If not, what are alternatives?

           

          And secondly, could someone advise how best to link to this Master_Date table to access the selector fields without the loops?  At some stage I will want to link the Timesheet_Hours table to a table (yet to be created) of orders processed via the Date Key to allow KPI calculations.

            • Re: List Boxes for Dates / Days / Weeks / Quarters / Years
              John Witherspoon

              Yes, you would normally create a calendar table, which is what your master date table is.  The calendar table would only link to the other tables by the date.  Looks like you've done that.

               

              Now your problem is the loop, right?  If the date on both tables has the same meaning, is the same date, then you only need it on one of the tables, and the loop is resolved that way.  If the two dates have a DIFFERENT meaning, such as, say, a "shipped date" vs. "received date", you would typically create TWO calendars, and resolve the loop that way. 

               

              There are ways to handle multiple dates on multiple tables all with the same master calendar, but they are more complicated and actually answer different kinds of questions than the multiple-calendar solution.  So what you need depends on what kinds of questions you want to ask of your data model.  Multiple calendars are probably far more common.  I'd suggest starting there.

                • Re: List Boxes for Dates / Days / Weeks / Quarters / Years
                  John Witherspoon

                  Oh, and I wouldn't build the calendar in Excel.  You're a QlikView programmer now, so program it in QlikView.    I personally turned my calendar into a QVD, and just load the fields in the user applications.  That helps standardize formatting and the like.  Here's my file that creates the calendar.  Mine references a database, but that's only to establish some fields you don't care about involving when we purge data from the live databases.  In any case, it's just a reference, not something you'd copy whole and entire.  Our week number, for instance, is neither the ISO nor the US standard.

                    • Re: List Boxes for Dates / Days / Weeks / Quarters / Years

                      Thanks John,

                       

                      I can uderstand that, but how would I handle the following simplistic scenario.

                       

                      I am in a warehouse scenario and I am creating a dashboard to calculate some efficiency KPIs.

                       

                      I import timesheets by date and department - and create a calendar in Qlikview for selecting by various fields as above.

                       

                      I also import inbound (import) and outbound (export) jobs - which also each have a date - and I can happily create a separate calendar for each one.

                       

                      But now I want to calculate the cubic meters processed by each department for each principle on each date.  I then want to work out the KPIs by dividing the CMB processed on a date by the hours worked on that date.

                       

                      How do I link the tables to allow the calculation?  Or do I just do a formula along the lines of:

                       

                           if(And(Inbound_Date=Outbound_Date,Inbound_Date=Worked_Date),sum(cbm)/sum(Worked_Hours))

                       

                      By the way, both inbound and outbound volumes will get calculated using a Product table which is the same for both Inbound and Outbound jobs.  By the same calendar logic, should I create a separate Inbound Product catalogue and a separate Outbound Product Catalogue - even though they are identical to avoid loops?

                       

                      Several times people have told me to try and design a star configuration.  Can you explain what that really means?

                       

                      Thanks

                      Ian