For Next Loop

[shumailh]

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

[shumailh]

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

[shumailh]

I think the possible way is

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

divide the expression with number of months

[stevedark]

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

[stevedark]

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

[#6]

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

[#7]

Great Steve,

Thanks a lot for the post,

[#8]

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 ???

[stevedark]

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

[stevedark]

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-Analys...