topic Formatting Dates for a Qlikview Doc in QlikView

Formatting Dates for a Qlikview Doc

Fri, 20 Aug 2010 22:06:20 GMT

Working on my first Qlikview document. The dates in my sales report are in MM/DD/YYYY format.

I would like to set up a list box that will allow filtering sales by month. When I create the list box, I get all of the months repeated for each sales in the applicable month. So my list box has 10 Januarys, 13 Februarys, 6 Marchs, etc.

How do I correct this in the script, so I only get one of each month, and when that month is selected by the users, all of the sales in that month are highlighted in the document.

thank you.

Formatting Dates for a Qlikview Doc

vgutkovsky — Fri, 20 Aug 2010 22:12:14 GMT

Well, the syntax is month(datefield). Is that not what you have currently?

Regards,

Formatting Dates for a Qlikview Doc

Fri, 20 Aug 2010 22:17:21 GMT

The only thing I have at this point is the date field EngagementDate being pulled in from the load script. I'm using that field in a List box and have modified the format so that it displays in MMM format. However, the EngagementDate field contains the MM/DD/YYYY from the database. Its not broken out.

I am a very new user and have not attended a training session yet. Just building a document using the reference material contained with the application.

Formatting Dates for a Qlikview Doc

vgutkovsky — Fri, 20 Aug 2010 22:35:44 GMT

Create a list box that lists an Expression. The expression would then be the following: month(EngagementDate). If this does not work, then your date field is not formatted properly as a date and you should fix that in the script.

Regards,

Formatting Dates for a Qlikview Doc

johnw — Fri, 20 Aug 2010 23:25:45 GMT

To explain further, MMM is just a format. It isn't changing the underlying value of the field, which is still a date. It is only changing how the date is displayed. The month() function, on the other hand, extracts the month from the date. So all dates in a month will have the same month(Date). For performance, it is usually better to establish a Month field in the script.

LOAD
...
,EngagementDate
,month(EngagementDate) as EngagementMonth
...

Sometimes this is done with a separate calendar table. Sometimes this field is just added to your main table. Either way works.

Formatting Dates for a Qlikview Doc

vgutkovsky — Fri, 20 Aug 2010 23:45:10 GMT

Thanks, John, that's an excellent explanation!

Formatting Dates for a Qlikview Doc

Sat, 21 Aug 2010 00:12:17 GMT

Thanks John

This worked perfectly and accomplished what I needed.

Formatting Dates for a Qlikview Doc

Sat, 21 Aug 2010 03:44:54 GMT

Al,

Search for "Date and Time Functions" in the QlikView Help file...you'll find a treasure trove of great operators to manipulate your date field...