Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

alexbjorlig
Contributor

Get Max and Min values from 4 weeks period

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.

Question 4 max and min.PNG

Tags (3)
1 Solution

Accepted Solutions

Re: Get Max and Min values from 4 weeks period

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


talk is cheap, supply exceeds demand
7 Replies
MVP
MVP

Re: Get Max and Min values from 4 weeks period

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

Re: Get Max and Min values from 4 weeks period

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


talk is cheap, supply exceeds demand
alexbjorlig
Contributor

Re: Get Max and Min values from 4 weeks period

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

Capture.PNG

alexbjorlig
Contributor

Re: Get Max and Min values from 4 weeks period

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

Capture2.PNG

MVP
MVP

Re: Get Max and Min values from 4 weeks period

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

alexbjorlig
Contributor

Re: Get Max and Min values from 4 weeks period

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

alexbjorlig
Contributor

Re: Get Max and Min values from 4 weeks period

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

Community Browser