above() function - please help!!

[#1]

Hi everybody,

I'm in quite a pickle trying to solve the following problem:

I have a chart, using only Month as a dimension. Using set analysis I then calculate the amount of picks for each respective month of the previous & current month. I then calculate an average, based on the amount of days worked within the relevant month. Like so:

For the blue i have used the following set analysis:

above(sum({<YEAR={'$(=vThisYear)'}>}[VALID PICK]) / count({<YEAR={'$(=vThisYear)'}>}distinct [DATE PICKED]),3,1) + // Average for 3 months back above(sum({<YEAR={'$(=vThisYear)'}>}[VALID PICK]) / count({<YEAR={'$(=vThisYear)'}>}distinct [DATE PICKED]),2,1) + // Average for 2 months back above(sum({<YEAR={'$(=vThisYear)'}>}[VALID PICK]) / count({<YEAR={'$(=vThisYear)'}>}distinct [DATE PICKED]),1,1) / 3 // Average for 1 month back. Divide by 3

This works fine, except for January - March, because it cannot use the above function to reference last year's averages.

Could someone please help me on how to figure out the issue with January - March's forecast??

Also, for the yellow (Jul - Dec):

I want to add 3 previous months & divide by 3 to give me an average. For example: 6148 (Aug) = 6746 (Jul) + 6982 (Jun) + 4716 (May) / 3

I have no problem with the if statements to differentiate between the blue & the yellow, but I do have an issue referencing the values calculated in the previous months.

Maybe I'm over-thinking the problem, maybe I'm just making things harder on myself by using "long ways" to do the calculations - anyone willing to help me on the right path?? Any help would be greatly appreciated.

Thanks!!!

[avastani]

use flags that are pre-scripted in your app and then you can you reference the flags in set analysis for your required date ranges. you can have flags for YTD, QTD, CY, LY, MTD, CM, LM, LYM etc...

[#3]

Very wise words. Thanks 🙂

What I've done is pre-calculate Oktober - Desember, so that when for example calculating the forecasted average for January, I can do the following:

(vOktAve + vNovAve + vDesAve) / 3

This works perfectly.

I still have some difficulty on how to do the yellow area