topic Re: Between Date in QlikView

Between Date

Anonymous — Fri, 09 Nov 2012 14:50:51 GMT

hi All,

I'm looking for a solution to get rang of date.

For exemple, i've data between 01/01/2012 and 01/12/2012

knowing that the date day is 01/12/2012.

i'm looking to get all the dates between 01/01/2012 and 01/06/2012.

any idea ?????

Re: Between Date

MayilVahanan — Fri, 09 Nov 2012 14:57:27 GMT

Are you looking this

Let vEnd = Num(date(01/12/2012));

Let vStart = Num(date( 01/06/2012));

Generate:

LOAD Date($(vStart) + IterNo()) AS Date

AutoGenerate 1

While ($(vStart) + IterNo()) <= $(vEnd);

Hope it helps

Re: Between Date

Anonymous — Fri, 09 Nov 2012 14:59:34 GMT

i'm trying to get the date in a field selection.

Re: Between Date

Anonymous — Fri, 09 Nov 2012 15:11:56 GMT

to give you an idea about what im looking to do, i tried this :

if(MOIS_ENCAISS>=AddMonths(MOIS_ENCAISS,-12) and MOIS_ENCAISS>=AddMonths(MOIS_ENCAISS,-6),MOIS_ENCAISS)

Re: Between Date

MayilVahanan — Fri, 09 Nov 2012 15:55:44 GMT

Try like this

=sum({<Date = {'>=$(=vVar1)<=$(=vVar)'}>}Sales)

vVar1

=AddMonths(Date,-6)

vVar

=AddMonths(Date,0)

Re: Between Date

CELAMBARASAN — Fri, 09 Nov 2012 15:58:32 GMT

Hi you need the dates between the 2 dates are performing calculation for these dates?

Re: Between Date

Anonymous — Fri, 09 Nov 2012 16:17:50 GMT

i gived up for this solution !!

now i created 2 variables, for exemple :

V1 = A

V2= F

i want to creat a macro how will select the data between V1=A & V2=F.

?????

Re: Between Date

Anonymous — Fri, 09 Nov 2012 16:31:25 GMT

i did this code , and it works fine :

sub DataBetweenSelectedDate() 'Used with the Variables , it filter the reports for two selected dates
set FD = ActiveDocument.Variables("FromDate") '' variable for Initial date
FrmDtt=(FD.GetContent.String)
FrmDate=">=" & Day(FrmDtt) & "/" & month(FrmDtt) & "/" & year(FrmDtt)
set TD = ActiveDocument.Variables("ToDate") '' Varible for End Date
ToDtt=(TD.GetContent.String)
ToDate="<=" & Day(ToDtt) & "/" & month(ToDtt) & "/" & year(ToDtt)
ActiveDocument.Fields("CommonDate").Select FrmDate & ToDate '' CommonDate is field same to select between FrmDtt to ToDtt
end sub

Re: Between Date

MayilVahanan — Fri, 09 Nov 2012 16:36:13 GMT

for this

FrmDate=">=" & Day(FrmDtt) & "/" & month(FrmDtt) & "/" & year(FrmDtt),

Try like this

FrmDate=">=" & Date(FrmDtt,'DD/MM/YYYY')

Hope this help

Re: Between Date

Clever_Anjos — Fri, 09 Nov 2012 16:39:01 GMT

You don´t need a macro to this, (If you can do something without a macro, don´t use it at all)

Use 2 variables and a inputbox or slider to set these variables.

For example:

D1 and D2 are the variables.

So a Set Analysis like below returns what you need

sum({< YourDate={">=$(=D1)"}*{"<=$(=D2)"}>}Sales)

Note that I´m using the intersection operator "*" instead of >= D1 <= D2