topic Incorrect month order in QlikView

Incorrect month order

cclayford — Wed, 02 Oct 2013 08:17:11 GMT

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 — Wed, 02 Oct 2013 10:07:43 GMT

Dear ,

Can you attach the sample file.

Thanks,

Mukram.

swuehl — Wed, 02 Oct 2013 10:14:46 GMT

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 — Wed, 02 Oct 2013 11:14:15 GMT

Thanks swuehl, that worked!

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

swuehl — Wed, 02 Oct 2013 11:21:07 GMT

Pretty similar:

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