Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
cclayford
Partner - Creator
Partner - Creator

Incorrect month order

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

Example.png

- 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?

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

4 Replies
mdmukramali
Specialist III
Specialist III

Dear ,

Can you attach the sample file.

Thanks,

Mukram.

swuehl
MVP
MVP

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
Partner - Creator
Partner - Creator
Author

Thanks swuehl, that worked!

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

swuehl
MVP
MVP

Pretty similar:

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