Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
shumailh
Creator III
Creator III

For Next Loop

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

18 Replies
shumailh
Creator III
Creator III
Author

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.

ValueJanFebMarAprMayJunJulTotal 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
Creator III
Creator III
Author

I think the possible way is

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


divide the expression with number of months

stevedark
MVP
MVP

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

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

Not applicable

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

Not applicable

Great Steve,

Thanks a lot for the post,

Not applicable

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

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