Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

OK, so you have three different date fields on either two or three different tables:

Inbound Date
Outbound Date
Worked Date

And you want to, say, select 2011-5-16, and then be able to calculate everything inbound, outbound and worked on that date?  In that case, you probably don't want the separate calendars I suggested, but rather a single calendar.  I explain how to create a single calendar for multiple fields on multiple tables in this thread:

http://community.qlik.com/message/94375

Unfortunately, the link to the final solution is no longer working, but the explanation should still be relevant.  I should put together an example of my own some day, I suppose.  Once you have your dates linked in the data model, you should be able to select a single value for your "Date" field, and then just do this:

sum(cbm)/sum(Worked_Hours)

As for a star configuration:

http://en.wikipedia.org/wiki/Star_schema

Though my own applications tend to more resemble a snowflake configuration:

http://en.wikipedia.org/wiki/Snowflake_schema

View solution in original post

7 Replies
Not applicable
Author

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.

johnw
Champion III
Champion III

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.

johnw
Champion III
Champion III

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.

Not applicable
Author

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

johnw
Champion III
Champion III

OK, so you have three different date fields on either two or three different tables:

Inbound Date
Outbound Date
Worked Date

And you want to, say, select 2011-5-16, and then be able to calculate everything inbound, outbound and worked on that date?  In that case, you probably don't want the separate calendars I suggested, but rather a single calendar.  I explain how to create a single calendar for multiple fields on multiple tables in this thread:

http://community.qlik.com/message/94375

Unfortunately, the link to the final solution is no longer working, but the explanation should still be relevant.  I should put together an example of my own some day, I suppose.  Once you have your dates linked in the data model, you should be able to select a single value for your "Date" field, and then just do this:

sum(cbm)/sum(Worked_Hours)

As for a star configuration:

http://en.wikipedia.org/wiki/Star_schema

Though my own applications tend to more resemble a snowflake configuration:

http://en.wikipedia.org/wiki/Snowflake_schema

millnet-maho
Partner - Contributor III
Partner - Contributor III

QlikTech should implement a smart date selector/calendar control that would allow the user to select all dates in a certain month, week or quarter of a certain year or of all years conveniently. That way all this mess with calendar tables and calendar linkage tables can be avoided.

johnw
Champion III
Champion III

I agree that QlikView's handling of date information leaves a lot to be desired.