topic Re: Loop Counter Value Calculation in QlikView

Loop Counter Value Calculation

Vinothishere — Thu, 20 Aug 2015 07:57:50 GMT

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,

Re: Loop Counter Value Calculation

alexandros17 — Thu, 20 Aug 2015 08:04:01 GMT

Try with this expression

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

Re: Loop Counter Value Calculation

sunny_talwar — Thu, 20 Aug 2015 08:06:58 GMT

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

sunny_talwar — Thu, 20 Aug 2015 08:21:27 GMT

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

MarcoWedel — Thu, 20 Aug 2015 17:59:38 GMT

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

Re: Loop Counter Value Calculation

Thu, 20 Aug 2015 19:40:22 GMT

Try like Below:

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

Re: Loop Counter Value Calculation

Vinothishere — Tue, 25 Aug 2015 18:57:42 GMT

Hi Marco,

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

Re: Loop Counter Value Calculation

Vinothishere — Tue, 25 Aug 2015 18:58:17 GMT

Thank you Alessandro.

Re: Loop Counter Value Calculation

Vinothishere — Tue, 25 Aug 2015 18:58:49 GMT

Thank you Sunny.

Re: Loop Counter Value Calculation

Vinothishere — Tue, 25 Aug 2015 18:59:11 GMT

Thank you dathu.