Get Max and Min values from 4 weeks period

[alexbjorlig]

Hi Qlik Community.

I have this following issue, i need to save max and min values in variables:

1. The User selects a week (week 17)
2. The table sums sales for the last 4 weeks (week 13- week 17)
3. How do I get the MAX and MIN values from this list(marked with red)?

I have attached screenshot and document to clarify.

(If you are interested the end goal is to use the information to axis dimensions in a chart).

Thanks many times in advance.

[swuehl]

You probably use an aggregation with set analysis in your expression, then your required Expression (e.g. used in a text box) might look

=Min( SetExpressionUsedInYourExpression Aggr( YourExpression, Week, [Employee Team]))

=Max( SetExpressionUsedInYourExpression Aggr( YourExpression, Week, [Employee Team]))

[Gysbert_Wassenaar · Accepted solution]

Try this in a text box:

='max: ' & max({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year>}

aggr(sum({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year>}Sales),Year,Week))

& chr(10) &

'min: ' & min({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year>}

aggr(sum({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year>}Sales),Year,Week))

[alexbjorlig]

Hi Swuehl, Thanks for your quick reply. It almost works?

[alexbjorlig]

Hi Gysbert. Thanks for the fast reply. The min value works, but not the max?

[swuehl]

Have you added the [Employee Team] to the aggr() dimensions?

[alexbjorlig]

I implemented your answer one more time, and this time it worked - thanks!

[alexbjorlig]

I came back to this question, and realized it only worked when I added the Employee Team, so the final answer look like this:

='min: ' & min({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year,[Employee Team]>}

aggr(sum({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year,[Employee Team]>}Sales),Year,Week,[Employee Team]))

='max: ' &

max({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year,[Employee Team]>}

Aggr(sum({<Date={">=$(=DATE(Max(Date)-7*4))<=$(=DATE(Max(Date)))"},Week,Year, [Employee Team]>}Sales),Year,Week,[Employee Team]))