Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Vinothishere
Contributor III
Contributor III

Loop Counter Value Calculation

Hi,

  I am trying to creating a new calendar which associates next three months dates for a current month.

  Ex: Aug month associated to Sep,Oct and Nov dates similarly Sep associated to Oct,Nov and Dec dates and so on.

  For creating this, the loop counter value should be sum of number of days in next three months as per my understanding.

 

so how to calculate the "sum of number of days in next three months"  in the while statement.

  i.e while iterno()<= Calculation condition.

Please help me out

Thanks in advance

Regards,

VR

1 Solution

Accepted Solutions
alexandros17
Partner - Champion III
Partner - Champion III

Try with this expression

=Floor(Num(MonthEnd(AddMonths(Today(),3)))) - Num(MonthStart(AddMonths(Today(),1)))

View solution in original post

9 Replies
alexandros17
Partner - Champion III
Partner - Champion III

Try with this expression

=Floor(Num(MonthEnd(AddMonths(Today(),3)))) - Num(MonthStart(AddMonths(Today(),1)))

sunny_talwar

I think you might be able to do this using AddMonths to create Min and Max Dates and then use IntervalMatch to get what you are looking for.

sunny_talwar

Have a look at the following script:

Table:

LOAD MonthStart(AddMonths(MonthYear, 1)) as Start,

  MonthEnd(AddMonths(MonthYear, 3)) as End,

  MonthYear;

LOAD MonthName(Date#(MonthYear, 'MMM-YYYY')) as MonthYear;

LOAD * Inline [

MonthYear

Jan-2015

Feb-2015

Mar-2015

Apr-2015

May-2015

Jun-2015

Jul-2015

Aug-2015

Sep-2015

Oct-2015

Nov-2015

Dec-2015

];

Temp:

Load min(Start) as minDate,

     max(End) as maxDate

Resident Table;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD Date($(varMinDate) + IterNo() - 1) as Date

AutoGenerate 1

While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

Left Join (Table)

IntervalMatch (Date)

LOAD Start,

  End

Resident Table;

MarcoWedel

Why are you trying this? You might be able to compare 4 consecutive months using a set expression instead of some special calendar fields.

Regards

Marco

Not applicable

Try like Below:

While IterNo() <= AddMonths(MonthStart(Today()),3) - Today() + 1;

Vinothishere
Contributor III
Contributor III
Author

Hi Marco,

I just wanted to implement this in the script level instead of using set expression.

Vinothishere
Contributor III
Contributor III
Author

Thank you Alessandro.

Vinothishere
Contributor III
Contributor III
Author

Thank you Sunny.

Vinothishere
Contributor III
Contributor III
Author

Thank you dathu.