Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

christophebrock
Contributor

Loop through months

Hi,

I need to put a loop on a LOAD statement with the following requirements:

  • A start & end date are defined as non-changing values
  • The loop needs to update 2 other variables which I need to include into the load statement
    • Variable 1: The 1st day of the month (in yyyy-mm-dd format)
    • Variable 2: The last day of the month (also in yyyy-mm-dd format)
  • The loop needs loop through all months starting with the month of the "startdate" and ending with the month of the "enddate" and generate the first and last day of each month into the variables (as described above)

Example:

Assume:

Startdate: 2013-01-01

End date: 2013-11-07

Values I want to generate in the first loop:

vSDate: 2013-01-01

vEDate: 2013-01-31

Values I want to generate in the second loop:

vSDate: 2013-02-01

vEDate: 2013-02-28

etc etc..., ending with in the last loop:

vSDate: 2013-11-01

vEDate: 2013-11-30

I was able to do this crawling through the loop day by day, using the formula below, but I'm unable to do the same to loop through the months:

for zi = date(vStartDateGA,'YYYY-MM') to date(vEndDateGA,'YYYY-MM')

        Let zDate = Date(zi,'YYYY-MM-DD');

        let eDate = MonthEnd(date(zi,'YYYY-MM-DD'));

next


Any help would be greatly appreciated.

Kind regards,

Christophe

Tags (2)
1 Solution

Accepted Solutions
Employee
Employee

Re: Loop through months

SET DateFormat='YYYY-MM-DD';

LET vStartDateGA = '2011-10-01';

LET vEndDateGA = '2013-12-01';

do while vStartDateGA <= vEndDateGA

   Let zDate = MonthStart(date('$(vStartDateGA)','YYYY-MM-DD'));

   let eDate = MonthEnd(date('$(vStartDateGA)','YYYY-MM-DD'));

   let vStartDateGA = date(AddMonths('$(vStartDateGA)',1),'YYYY-MM-DD');

  

loop 

5 Replies
Employee
Employee

Re: Loop through months

SET DateFormat='YYYY-MM-DD';

LET vStartDateGA = '2011-10-01';

LET vEndDateGA = '2013-12-01';

do while vStartDateGA <= vEndDateGA

   Let zDate = MonthStart(date('$(vStartDateGA)','YYYY-MM-DD'));

   let eDate = MonthEnd(date('$(vStartDateGA)','YYYY-MM-DD'));

   let vStartDateGA = date(AddMonths('$(vStartDateGA)',1),'YYYY-MM-DD');

  

loop 

Re: Loop through months

Do you mean variables or fields? If you create a loop that changes the value of a variable in each iteration then after the loop ends you will still have only one variable that will have only one value.

Actually, why don't you start by explaining what your source data is and what the end result is that you're looking for.


talk is cheap, supply exceeds demand

Re: Loop through months

This will always be true so the loop never ends: do while vStartDateGA <= vEndDateGA 


talk is cheap, supply exceeds demand
christophebrock
Contributor

Re: Loop through months

Worked like a charm ! Thanks !

Employee
Employee

Re: Loop through months

I´m overriding it inside the loop

let vStartDateGA = date(AddMonths('$(vStartDateGA)',1),'YYYY-MM-DD');

Community Browser