Formatting Dates for a Qlikview Doc

[#1]

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.

[vgutkovsky]

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

Regards,

[#3]

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.

[vgutkovsky]

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,

[johnw · Accepted solution]

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.

[vgutkovsky]

Thanks, John, that's an excellent explanation!

[#7]

Thanks John

This worked perfectly and accomplished what I needed.

[#8]

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...