Substrin a Date field

Tue, 01 Feb 2011 18:12:33 GMT

Question 1.: How do you substring out a date files so each of it's components can be used in list boxes to control the drill down. I have a single date files 12/31/2010. I want to be able to select based on Year, Month or day from a list box

Question 2. Using a calendar, how can you select data within a date range? For example I want to look at revenue between 12/1/2010 and 12/15/2010.

Thanx

Substrin a Date field

matt_crowther — Tue, 01 Feb 2011 18:21:21 GMT

Try: =month(Date_Field) or =month(Date_Field) or =monthname(Date_Field) or day(Date_Field). Have a look under the 'Date & Time' functions of the expression window.

A little trickier. My version is: create 2 variables (vStartDate and vEndDate) create 2 calendar objects each linked to a variable. In your chart the expression would be something along the lines of: =sum(if(Date_Field>=vStartDate and Date_Field <=vEndDate,Sales)) - that's un-checked syntax but should work.

It's a bit tricky setting the calendar scales but I usually just add =min(Date_Field) as the min and =max(Date_Field) as the max...so no that tricky at all really!?

This can also be achieved with Set Analysis and simple input boxes instead of calendar objects.

Hope that helps,

Matt - Visual Analytics Ltd

topic Substrin a Date field in QlikView

Substrin a Date field

Substrin a Date field