topic Looping between months in QlikView

Looping between months

Wed, 16 Mar 2016 14:05:26 GMT

Hi,

I have the sales of 10 stores saved in a table for every month; in other words, in table Sales1601 there are the sales that the 10 stores made in January '16, in table Sales1602 there are the sales that the 10 stores made in February '16, in table Sales1501 there are the sales that the 10 stores made in January '15 exc.

I'd like to have a script that's able to read the current month(Ex: March) and load all the data referring to the previous months (February and January), for the current year and also for the previous year.

Someone knows how to do it?

Thanks.

Re: Looping between months

amayuresh — Thu, 17 Mar 2016 06:12:20 GMT

Can use expression as

=sum({<Date={'>$(=MonthStart(Addmonths(Max(Date),-2)))'}>}Sales)

Re: Looping between months

manojkulkarni — Thu, 17 Mar 2016 06:35:26 GMT

Use Today() function to get current month year in required format. eg:

Date(Today(),'YYMM') which will return 1603

use loop to get all the numbers associated with tables like 1501,1601,1602 etc which are lessthan current month year number & inner loop use these number to fetch data from specific tables.

Re: Looping between months

Gysbert_Wassenaar — Thu, 17 Mar 2016 07:18:34 GMT

Can you explain what you're trying to do? Do you want to load source data into the qlikview document? Or have you already loaded the data in the qlikview document and are you asking about creating an expression for a chart?

Can you post a small qlikview document that demonstrates the problem?

Re: Looping between months

Thu, 17 Mar 2016 08:35:09 GMT

Hi Gysbert,

I want to load source data into the qlik document.

I don't know how to "write" the script in order to load all the months before the actual.

Re: Looping between months

Gysbert_Wassenaar — Thu, 17 Mar 2016 14:55:31 GMT

Perhaps like this:

LOAD * FROM mysource WHERE Date < today();