topic Re: Solution required in QlikView

Solution required

Thu, 30 Jul 2015 16:55:02 GMT

I am writting an application where i need to loop thru past 4 months from a given month one at a time in a query.

I am not sure what approach to take. Query requires variable in format like "YYYYMMDD".

Example 1: Example since current month is July, variable will loop thru 201507, 201506, 201505, 201504.

Exampl 2: We also have to be careful on year change. so if we are in Feb 2015. then loop thru will be 201502, 201501, 201412, 201411.

How can you do this in qlikview and supply the variable based on above requirements.

Thanks for your help.

Re: Solution required

Gysbert_Wassenaar — Thu, 30 Jul 2015 17:08:20 GMT

Can you explain 'need to loop'? What needs to be looped?

Re: Solution required

maleksafa — Thu, 30 Jul 2015 17:20:36 GMT

what are you trying to accomplish? if you want rolling month n-4 for an expression to accumulate the values, you can do that in the chart (Bar, Straight table, pivot etc...)

Re: Solution required

Thu, 30 Jul 2015 17:30:21 GMT

Example:

Today= 20150730

hence query needs to run for 201507, 201506, 201505, 201504.

within query you will pass variable like below:

20150401 to 20150431

20150501 to 20150531

20150601 to 20150631

20150701 to 20150731

Re: Solution required

stevedark — Thu, 30 Jul 2015 17:38:09 GMT

Is this because the data source you are using only serves up one month at a time?

What is the query you are using?

This code will loop in the load script, back a set number of months and place the month value in the format you need in a variable. This can be used as a parameter to a load query. In this code it just drops the variable in a table, so you can see the results:

let vMonthsBack = 24;

for i = 0 to vMonthsBack

let vNextMonth = Date(AddMonths(monthstart(today()), -i), 'YYYYMM');

// place in a table to test

LOAD $(vNextMonth) as Month autogenerate(1);

next

As gwassenaar‌ says though, I'm not sure quite what your requirement is, so this may or may not help.

Steve

Re: Solution required

Gysbert_Wassenaar — Thu, 30 Jul 2015 17:38:37 GMT

What is the input data and what is the end result? I have no idea if you're trying to program a procedure or create a chart that displays data.

Re: Solution required

sasiparupudi1 — Thu, 30 Jul 2015 17:53:07 GMT

Example loop

let vstartDate=Date#('201503','YYYYMM');

for i=1 to 4 step 1

let v$(i)=Date(AddMonths(vstartDate,-i),'YYYYMM');

next i;

Re: Solution required

Thu, 30 Jul 2015 20:14:04 GMT

Is this because the data source you are using only serves up one month at a time? => Yes

your loop statement does not handle year change if 4 months back from today's date fall in previous year.

Re: Solution required

stevedark — Thu, 30 Jul 2015 22:35:01 GMT

Hi,

The code will quite happily deal with going back past the beginning of the year. It is not just subtracting 1, it is subtracting one month and then formatting that correctly.

Please copy and paste the code you need to execute in order to pull back each month, and someone will be able to show you how to use a variable, derived in a loop, to pass a parameter to your data query.

Steve