topic For Next Loop in QlikView

For Next Loop

shumailh — Sun, 10 Jul 2011 13:24:55 GMT

The below expression is not working, please help?

=(
if(
Month(LoadDate_Active)>1
,
Let c=0
for a = Month(LoadDate_Active) to 1 step -1
Let c = $(c) + sum({$<AIF = {P}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)
next
$(c)
,
sum({$<AIF = {P}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)
)
)
/(100*1000000)

Shumail

For Next Loop

nagaiank — Sun, 10 Jul 2011 17:16:48 GMT

Where are you using this expression?

Set expressions do not work in load script and for ...next loop is not available in chart expressions.

For Next Loop

shumailh — Mon, 11 Jul 2011 05:12:08 GMT

Is there any alternate of "For..Next" loop in chart expression?

Also

Is there ne matrix for all these details I mean something like

	Load Script	Expression	Macro (vbscript)
For Next Loop	Yes	No	Yes
Do While Loop	Yes	No	Yes
Set	No	Yes	Yes

Shumail

For Next Loop

kaushiknsolanki — Mon, 11 Jul 2011 06:15:01 GMT

Hi,

As krishnamoorthy said, the looping can not be done in expression.

There is no matrix available like you shown.

Why you want to use for loop in expression.

Provide us your problem definition, may be there are some other solution available.

Regards,

Kaushik Solanki

For Next Loop

shumailh — Mon, 11 Jul 2011 06:32:31 GMT

Actually I am looking for the sum of customer balances by year like if i select May-2011 then expression can sum all the values in 2011 before and in may-2011 excluding june-2011 onward. similarly with every month.

I think I have to apply many nested If conditions like this

if(Month(LoadDate_Active)=1

,sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

,if(Month(LoadDate_Active)=2,

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate,-1))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

,if(Month(LoadDate_Active)=3,

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate,-1))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate,-2))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

...

.... till the twelve months

,if(Month(LoadDate_Active)=12,

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate,-1))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

...

....

sum({$<AIF = {P}, {$<MonthName_ID = {'$(=MonthName(LoadDate,-11))'}, Bucket_MOM = {0,1,2,3,4,5,6}>} bal)

)

/(100*1000000)

Shumail

For Next Loop

kaushiknsolanki — Mon, 11 Jul 2011 06:40:56 GMT

Hi,

Let me say what i understood from this is that,

when you select May-2011, you want data from Jan-2011 till May-2011.

Am i right.

Regards,

Kaushik Solanki

For Next Loop

ToniKautto — Mon, 11 Jul 2011 07:39:47 GMT

Generally it is really not recommende to use to many nested IF statements, since this can have serious impact on the application performance. You are much better of if you can find a solution using set analysis and/or Aggr().

An other option can be to do some additonal calculations in the load script, so that parts are statically precalculated and only needs to be sumed in the expression. This would be of some performance gain in case you run larger data volumes or a slower machine.

It would be easier to advise on a good approach if you attach a sample of you solution, so that the data model can be evaluated.

Re: For Next Loop

shumailh — Mon, 11 Jul 2011 10:42:10 GMT

Yes exactly you are right kaushik!

Thanks for the reply Toni, I am attaching one sample of my project. I am having problem on disabled field COF Final... Please see it on expression tab and if you have any solution then let me know... Thanks in Advance.

Shumail

Re: For Next Loop

shaunsomai — Mon, 11 Jul 2011 20:02:10 GMT

I have a solution but i cant seem to upload to the forum.

send me your email and i wil send the solution.

Try somehting like this in your script

I use it together with a calendar

I used these with set analysis to calculate some expressions in my chart(i.e. YTD,MTD,WTD...etc)

IF(M>3,Y+1,Y)as FinYear,

NUM(IF(M>3,Month(AddMonths(D,-3)),Month(AddMonths(D,9))),'00')as FinMonth,

YearName(D,0,4) as FinPeriod,

'Q' & Ceil(Month(addmonths(D,-3))/3) as Quarter,

NUM(IF(M>3,Y+1,Y)& Ceil(Month(addmonths(D,-3))/3),'00') as FinYearQuarter,

IF(M>3,Y+1,Y) & NUM(IF(M>3,Month(AddMonths(D,-3)),Month(AddMonths(D,9))),'00') as FinYearMonth,

Re: For Next Loop

stevedark — Mon, 11 Jul 2011 22:25:13 GMT

I may be missing something but is this not a simple pivot with Months as the dimension along the top and then Set Analysis in the expression to limit to the max year and months prior to the max selected?

Something like:

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month=>}Value)

I've also attached an example that may help.

By extending the example to also be aware of the Prior year you could put in a YTD prior year comparison with variances.

- Steve

Re: For Next Loop

shumailh — Wed, 13 Jul 2011 10:41:02 GMT

Steve,

Thanks for your helpful response and the attached example. it resolves one part of my problem

but the thing is i wanted to do the average of these values by month

like if the selected month is july 2011 in your example

then average should be the

sum of values of each month divided by 7 i.e. 13,782.86.

Value	Jan	Feb	Mar	Apr	May	Jun	Jul	Total	Average
Total	18,238.00	15,172.00	17,612.00	14,284.00	12,064.00	13,854.00	5,256.00	96,480.00	13,782.86

not

the sum of each row / number of rows

Shumail

Shaun my email is shumail.hussain@fgb.ae

Re: For Next Loop

shumailh — Wed, 13 Jul 2011 11:07:10 GMT

I think the possible way is

sum({<Year={$(vMaxYear)},Date={'<=$(vMaxDate)'},Month=>}Value)/vMaxMonth

divide the expression with number of months

Re: For Next Loop

stevedark — Wed, 13 Jul 2011 23:11:37 GMT

That looks like it should work, but will divide every month by the number of months - so your average will be correct but not the monthly columns. The other thing that would not be as your example above is having both a Total and an Average column. This is not easy (possible?) in a pivot.

What you could do is change it to a straight table and have a separate expression for each month, and then separate expressions for the Total and Average fields. You could then change the background and text settings for those expressions to make it clear they are totals. Finally you would need to conditionally show or hide the months (unless you wanted to show the zero future months).

I've added another tab to my example showing the straight table approach.

- Steve

Re: For Next Loop

stevedark — Mon, 18 Jul 2011 20:10:18 GMT

I've uploaded the example as a Shared QlikView - if you download it from there it is Personal Edition enabled:

http://community.qlik.com/qlikviews/1113

- Steve

Re: For Next Loop

Wed, 03 Oct 2012 19:01:40 GMT

I am trying for the year to date in pivote table, but I am getting no data in the pivot table.I am attaching my qv file can you please verify and reply back

Re: For Next Loop

Wed, 23 Oct 2013 05:59:15 GMT

Great Steve,

Thanks a lot for the post,

Re: For Next Loop

Wed, 03 Sep 2014 06:25:59 GMT

Good day , I want to up date the , Simple Year To Date Example.qvw to current year it just stop to 2011 , please help urgently ???

Re: For Next Loop

stevedark — Wed, 03 Sep 2014 06:37:29 GMT

Hi Steven,

The data is autogenerated based on the current date. Just do a reload and it should bring you right up to date.

I've just noticed the download link I posted previously has broken, the app can now be found here:

QlikView App: Simple Year To Date Example (Set Analysis)

Hope that helps,

Steve

Re: For Next Loop

stevedark — Sat, 24 Nov 2018 15:17:48 GMT

With the latest update it's now moved to here:

https://community.qlik.com/t5/QlikView-Documents/QlikView-App-Simple-Year-To-Date-Example-Set-Analysis/ta-p/1482919