Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Can you explain 'need to loop'? What needs to be looped?
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...)
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
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
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.
Example loop
let vstartDate=Date#('201503','YYYYMM');
for i=1 to 4 step 1
let v$(i)=Date(AddMonths(vstartDate,-i),'YYYYMM');
next i;
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.
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