Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

vinoth25593
New 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

Re: Loop Counter Value Calculation

Try with this expression

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

9 Replies

Re: Loop Counter Value Calculation

Try with this expression

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

Re: Loop Counter Value Calculation

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.

Re: Loop Counter Value Calculation

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;

Re: Loop Counter Value Calculation

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

Re: Loop Counter Value Calculation

Try like Below:

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

vinoth25593
New Contributor III

Re: Loop Counter Value Calculation

Hi Marco,

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

vinoth25593
New Contributor III

Re: Loop Counter Value Calculation

Thank you Alessandro.

vinoth25593
New Contributor III

Re: Loop Counter Value Calculation

Thank you Sunny.

vinoth25593
New Contributor III

Re: Loop Counter Value Calculation

Thank you dathu.