Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

average value of a month calculated through values from last 3 months

hi community,
maybe someone can help me?
objective:
the average value of a month is calculated thru its last 3 months values, depending on different machine id's and selected years.

problem:
with the accumulation 3 steps back and the division by 3 in the expression, i get the right values for months greater february (2011 is selected). for the correct values in january and february i would need values from december and november 2010. the dimension of the chart should be month.

how can i solve this problem?
i have tried various setanalysis expressions, but i couldnt find the right solution. i also thought on precalculating the values in script, but found no solution.
the selection of a machine id or a year should be possible.
t hanks for every help.
ciao christoph
1 Solution

Accepted Solutions
Not applicable
Author

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()

View solution in original post

6 Replies
Stefan_Walther
Employee
Employee

Hi Christoph,

please have a look at the attached solution for your challenge.

Regards

Stefan

Not applicable
Author

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

SunilChauhan
Champion
Champion

try this

avg({<MonthYear={"$(>=MonthName(addmonth(MonthYear,-3)))<=MonthName(addmonth(MonthYear,-1)))"}}<} Value)

hope this helps

Sunil Chauhan
Not applicable
Author

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

Not applicable
Author

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()

Not applicable
Author

THANKS Michael!!!

Thats exactly what i have looked for!

Perfect explanation!

regards Christoph