Addmonths in Set Analysis ( next 3 months)

[#1]

Hello,

Guys need your help!

I want to create an expression with average of next three months. I tried to use this one with addmonths function, but it's not working

Avg({<Month_name={">=$(=Date(addmonths(Month_name,1),'MMM'))<=$(=Date(addmonths(Month_name,3),'MMM'))"}>} Values)

Please help!

[swuehl]

You're applying a numeric search in your element list. Does field Month_name have a numeric representation (is it a dual value with a text and a numeric value, the numeric value showing monthstart date?

Maybe this is of some help:

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

[PrashantSangle]

Hi,

How did you create Month_name field or it is already availble in script???

If you are creating Month_name using Month(datefield)

then try

Avg({<datefield={">=$(=Date(addmonths(datefield,1)))<=$(=Date(addmonths(datefield,3)))"},Month_name=>} Values)

Regards

[#4]

Hi,

i tried this one Avg({$<Month_Date={">=$(=addmonths(Month_Date,1))<=$(=addmonths(Month_Date,3))"},Month_Date=>}Bench_Data)

and it worked but incorrectly.

The thing is the user picks several months in the filter like : from Jan till Jun, from Jan till July etc. And I want the table to show the average of next 3 months.

For example, if the user selects the months from January till June, I want it to show the average of values starting from July till September.

[#5]

Month_name field was created in the script

[antoniotiman]

Hi Diana,

add Max() in AddMonths

AddMonths(Max(Month_Date),1)  and ,3 (maybe 4)

and remove Month_Date= from set analysis

Regards,

Antonio

[PrashantSangle]

Hi,

Use max() to get max of selected months

try like

Avg({$<Month_Date={">=$(=addmonths(max(Month_Date),1))<=$(=addmonths(max(Month_Date),3))"},Month_Date=>}Bench_Data)

Regards

[#8]

It doesnt seem to work correctly, I checked again, and it gives me the wrong result. It seems to get the average of the max month. Like if you choose June for example, it gives you the average of previous months, from January till June... This is odd

[PrashantSangle]

Hi,

Is it possible to share app???

debug your code by remove label name of expression then on front end put pointer on that expression,

You will get value in set expression.

Just check it ,what it is showing???

And possible post screen shot of it

Regards

[#10]

Max,

Sorry, I dont understand how to make all these corrections.

Here is my sample app, would be greateful for help.

Thank You! ! !