topic Re: Get Max and Min values from 4 weeks period in QlikView

Get Max and Min values from 4 weeks period

alexbjorlig — Thu, 22 Oct 2015 08:29:10 GMT

Hi Qlik Community.

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

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 — Thu, 22 Oct 2015 09:17:12 GMT

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 — Thu, 22 Oct 2015 09:25:15 GMT

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 — Thu, 22 Oct 2015 09:26:11 GMT

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

alexbjorlig — Thu, 22 Oct 2015 09:31:33 GMT

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

swuehl — Thu, 22 Oct 2015 12:34:44 GMT

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

alexbjorlig — Thu, 22 Oct 2015 14:14:58 GMT

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

alexbjorlig — Mon, 26 Oct 2015 09:37:17 GMT

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