
Re: average value of a month calculated through values from last 3 months
Stefan Walther Dec 28, 2011 5:29 PM (in response to Christoph Schnauder )Hi Christoph,
please have a look at the attached solution for your challenge.
Regards
Stefan

Calc 3 months Avg_Loesung.qvw 167.8 K

Re: average value of a month calculated through values from last 3 months
Christoph Schnauder Dec 30, 2011 4:50 AM (in response to Stefan Walther )Hi Stefan,
The min() and max() function delivers only the actual month per dimension. so the solution only divides the actual months values by 3. Any other ideas?
Thanks for your help.
regards Christoph

average value of a month calculated through values from last 3 months
Sunil Chauhan Dec 30, 2011 5:02 AM (in response to Christoph Schnauder )try this
avg({<MonthYear={"$(>=MonthName(addmonth(MonthYear,3)))<=MonthName(addmonth(MonthYear,1)))"}}<} Value)
hope this helps

average value of a month calculated through values from last 3 months
Christoph Schnauder Dec 30, 2011 5:29 AM (in response to Sunil Chauhan)thanks for your help!
but its the same problem like before with the solution from stefan.
the dimension is monthname() and so every set analysis expression only calculates the actual value, like "Jun 2011".

Re: average value of a month calculated through values from last 3 months
Michael Brückner Dec 30, 2011 9:34 PM (in response to Christoph Schnauder )The Problem is that you have to calculate values not in your selection
So, you could use the formula below to calculate the missing values of the one and/or two prior months and then build your own average (no QV Accumulation cause this functions can only use values "visible" within the chart)
rangesum(
above(Sum(Turnover),0,3)
, if(RowNo()<3, sum( { 1 <Machine=P(Machine), _EMonthNum={$(#=addmonths(MIN(_EMonthNum),1))} >} TOTAL Turnover))
, if(RowNo()<2, sum( { 1 <Machine=P(Machine), _EMonthNum={$(#=addmonths(MIN(_EMonthNum),2))} >} TOTAL Turnover))
)
/ 3
The Above() sums the three prior months (and, if in row one only the actual and if in row two the actual and prior month), the next two IF(...) lines check if you in the first or second row and then calculate the needed months value from the month before.
Important is that your Sort order is Date Asscending. With some extra work i'm sure we could make that dynamically within the formula (If (...) and Above/below ... etc).
So, that formula should work also if you select a time slice within a year.
An other way could be to make an aggregation within the script to calculate the values and store them within an extra field. Therewith you don't have to use Set Analysis which makes it more easyally to understand what happend within the chart .
But, while scripting you not so flexible.
Pay special attention to the P() Function. I'm using that cause as far as i understood the Machine is the only selection used in the chart. If you use also other Filters you should include them in the Set Analysis as demonstrated with the Machine field.
Also be aware that probably the first two average value of all are not correct (cause the first Month value is divide by 3 as the Average of the prior three month). if you describe what the right value has to be we could have a look to include that. But at least, it' now working near to that you asked for
If you need more help don't hesiate to ask me.
Best regards
Michael
Nachricht geändert durch Michael_Brueckner: Syntax and a bit more detailed what not included and regarding P()

Re: average value of a month calculated through values from last 3 months
Christoph Schnauder Jan 2, 2012 2:48 AM (in response to Michael Brückner )THANKS Michael!!!
Thats exactly what i have looked for!
Perfect explanation!
regards Christoph




