# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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

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

## 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
Contributor

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?

MVP

## Re: Get Max and Min values from 4 weeks period

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

Contributor

## Re: Get Max and Min values from 4 weeks period

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

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