Create For Loop with MonthNames

karan_kn — Sat, 16 Nov 2024 00:17:38 GMT

We need to create For Loop Jan 20 to Oct 20 and Last 5 year date.

T1: //Jan 20
Load ID,
[Calendar Month]
Resident Table1 Where [Calendar Month]='Jan 20';

Join
Load
SaleDate,
ID
Resident table2 Where SaleDate>='01/01/2015' and SaleDate<='12/31/2019'; //Last five year

T1: // Feb 20
Load ID,
[Calendar Month]
Resident Table1 Where [Calendar Month]='Feb 20';

Join
Load
SaleDate,
ID
Resident table2 Where SaleDate>='02/01/2015' and SaleDate<='01/31/2020'; //Last five year

Re: Create For Loop with MonthNames

Saravanan_Desingh — Wed, 22 Jul 2020 03:01:22 GMT

Try this.

Cal: LOAD Date(MakeDate(2020,IterNo(),1),'MMM-YY') As CalMonth, Date(AddMonths('1/1/2015',IterNo()-1)) As StartDate, Date(AddMonths('12/31/2019',IterNo()-1)) As EndDate AutoGenerate 1 While IterNo()<=10; For i = 1 To 10 Let vCalMonth=Peek('CalMonth',$(i)-1); Let vStartDate=Peek('StartDate',$(i)-1); Let vEndDate=Peek('EndDate',$(i)-1); TRACE vCalMonth=$(vCalMonth), vStartDate=$(vStartDate), vEndDate=$(vEndDate); T1: Load ID, [Calendar Month] Resident Table1 Where [Calendar Month]='$(vCalMonth)'; Join (T1) Load SaleDate, ID Resident table2 Where SaleDate>='$(vStartDate)' and SaleDate<='$(vEndDate)'; //Last five year Next i Drop Table Cal;

Data in Cal:

topic Create For Loop with MonthNames in QlikView

Create For Loop with MonthNames

Re: Create For Loop with MonthNames