Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

compare QVD and get Previous 12 months of data from Current

Hi Friends Stuck with Date Functions,I Have a Shipping Period Field with all Dates the Format is

0201406,

0201405,

2014050

2014060

1)How to get into YYYYMM like 201406

2)We will store data in separate QVD for each year based on business requirement

2012.qvd

2013.qvd

2014.qvd

How to get past 12 months of data from current date,my problem is if i have all data in one qvd i have written as follow

vCurrent=MonthEnd(MONTHSTART(TODAY())-1)

vPrevious=AddMonths(MONTHSTART(Today()),-13)

Where [Shipping Period]>=$(vPrevious) and [Shipping Period]<=$(vCurrent)

How to check Different QVD and Get data from current date.

Suppose need to see date of Today() to previous 12 months of Date

Nov-2014 to Nov-2013 here need to compare 2 Qvds

4 Replies
sergey_maka8
Valued Contributor

Re: compare QVD and get Previous 12 months of data from Current

Hi,

1) Just an example =DATE(Today(),'DDMMYYYY')

2)Apply DATE(MonthEnd(MONTHSTART(TODAY())-1),'DDMMYYYY')

DATE(AddMonths(MONTHSTART(Today()),-13),'DDMMYYYY')

Regards,

Sergey

Not applicable

Re: compare QVD and get Previous 12 months of data from Current

Not Expecting in this way how to change below format of

0201406,

0201405,

2014050

to YYYYMM as 201406

2)how to get past 12 months of data from qvd where for each Year 1 Qvd how to get  data in a loop. 

sergey_maka8
Valued Contributor

Re: compare QVD and get Previous 12 months of data from Current

Date(Date#(DateField,'DDMMYYYY'),'YYYYMM')

sergey_maka8
Valued Contributor

Re: compare QVD and get Previous 12 months of data from Current

2) If you have not really big qvd you can use just *.qvd (qvd) instead of actual file name,

If they are big you just need to calculate years and put variable as filename.

vCurrentYear=Year(Today);

For i=$(vCurrentYear)-1 to $(vCurrentYear)

LOAD *

FROM $(i).qvd (qvd)

WHERE [Shipping Period]>=$(vPrevious) and [Shipping Period]<=$(vCurrent);

Next i

Community Browser