Formatting a Filter on a DATA Field

Fri, 21 Dec 2012 10:02:06 GMT

HI,

I put this Marco in the report to select a Date Range(7 days,7 week, 7month ,7 year before the TDate variable based ion a variable PERIODDESC selected)

Sub FilterData

set i = ActiveDocument.Fields("PERIODDESC").GetSelectedValues

set v2 = ActiveDocument.Variables("TDate")

if i.count >0 then

if i(0).Text ="DAY" then

ActiveDocument.Fields("DATEPERS").Clear

ActiveDocument.Fields("DATEPERS").select cstr(">=" & cstr(day(dateadd("d",-7,CDATE(v2.Getcontent.String))) & "/" & month(dateadd("d",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("d",-7,CDATE(v2.Getcontent.String)))) & " <=" & cstr(day(CDATE(v2.Getcontent.String)) & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

elseif i(0).Text ="WEEK" then

ActiveDocument.Fields("DATEPERS").Clear

ActiveDocument.Fields("DATEPERS").select cstr(">=" & cstr(day(dateadd("d",-56,CDATE(v2.Getcontent.String))) & "/" & month(dateadd("d",-56,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("d",-56,CDATE(v2.Getcontent.String)))) & " <=" & cstr(day(CDATE(v2.Getcontent.String)) & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

elseif i(0).Text="MONTH" then

ActiveDocument.Fields("DATEPERS").Clear

ActiveDocument.Fields("DATEPERS").select cstr(">=" & cstr(day(dateadd("m",-7,CDATE(v2.Getcontent.String))) & "/" & month(dateadd("m",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("m",-7,CDATE(v2.Getcontent.String)))) & " <=" & cstr(day(CDATE(v2.Getcontent.String)) & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

else

ActiveDocument.Fields("DATEPERS").Clear

ActiveDocument.Fields("DATEPERS").select cstr(">=" & cstr(day(dateadd("yyyy",-7,CDATE(v2.Getcontent.String))) & "/" & day(dateadd("yyyy",-7,CDATE(v2.Getcontent.String))) & "/" & year(dateadd("yyyy",-7,CDATE(v2.Getcontent.String)))) & " <=" & cstr(day(CDATE(v2.Getcontent.String)) & "/" & month(CDATE(v2.Getcontent.String)) & "/" & year(CDATE(v2.Getcontent.String))))

end if

end Sub

The TDate Variable is in DD/MM/YYYY but when i use a function DAY() in my PC wiith regional setting MM/DD/YYYY the functioon return the month:

for ex:

Day(12/03/2012) return 03.

how I can do this?

Many thanks

Re: Formatting a Filter on a DATA Field

er_mohit — Fri, 21 Dec 2012 12:09:49 GMT

hey you can try this code might be helpful

mid( Date, index( Date, '-', 2 ) -2, 2 ) where Date = 1997-07-14 returns 07

topic Formatting a Filter on a DATA Field in QlikView

Formatting a Filter on a DATA Field

Re: Formatting a Filter on a DATA Field