Incorrect month order

[cclayford]

Hi

I have two expressions calculating averages over 3 months.

The first one calculates the average for the first 3 months:

sum({<Month={'>=$(=min(Month))<=$(=min(Month)+2)'}>}[Total Units]) / 3

The second one calculates the average for the last 3 months:

sum({<Month={'>=$(=max(Month)-2)<=$(=max(Month))'}>}[Total Units]) / 3

In the picture bellow you will notice that there are some anomalies

- For some reason it is calculating box A as the average for the last 3 months.

- It is also calculating box B as the average for the first 3 months.

- Ideally i would like it to calculate box C as the average for the first 3 months, and box D as the average for the last 3 months.

Please can you help me achieve this?

[mdmukramali]

Dear ,

Can you attach the sample file.

Thanks,

Mukram.

[swuehl · Accepted solution]

I think it's calculating correctly the values for the first three months (Jan - Mar) and last 3 months (Oct - Dec).

I assume you want to get the first 3 YearMonths and last three YearMonths? Then use something like

sum({<YearMonth={'>=$(=min(YearMonth))<=$(=addmonths(min(YearMonth),2))'}>}[Total Units]) / 3

You can create a YearMonth field in the script using e.g. MonthName() (but it seems you are already using a field like this).

[cclayford]

Thanks swuehl, that worked!

Can you send me the expression for the last three months as well?

[swuehl]

Pretty similar:

sum({<YearMonth={'>=$(=addmonths(max(YearMonth),-2))<=$(=max(YearMonth))'}>}[Total Units]) / 3