topic Finding MIN and MAX in QlikView

Finding MIN and MAX

Mon, 26 Nov 2012 11:01:43 GMT

Hi All,

I have report which looks like -

Product_Code	Customer_Code	Region	sum(Sales)
A	ABC	EAST	1200
A	DEF	EAST	1400
A	MNO	EAST	1600
B	ABC	WEST	1800
B	DEF	WEST	2000
B	MNO	SOUTH	2200

and i want the output like -

Product_Code	Customer_Code	Region	sum(Sales)	Min(Sales)	Max(Sales)
A	ABC	EAST	1200	1200	1600
A	DEF	EAST	1400	1200	1600
A	MNO	EAST	1600	1200	1600
B	ABC	WEST	1800	1800	2200
B	DEF	WEST	2000	1800	2200
B	MNO	SOUTH	2200	1800	2200

Here MIN(Sales) and MAX(Sales) are computed based on Product_Code.

E.g. In the above report, for Product_Code = A, the minimum sales is 1200 and maximum sales is 1600. This should be computed by first doing a GROUP BY on Product_Code, Customer_Code, Region. Then we need to calculate MIN and MAX on the output of this Group.

Please help me how to achieve this.

Regards,

Samir.

Re: Finding MIN and MAX

perumal_41 — Mon, 26 Nov 2012 11:24:40 GMT

Try this expression

min(Total <Product_Code>Sales)

max(Total <Product_Code>Sales)

Re: Finding MIN and MAX

Mon, 26 Nov 2012 12:04:05 GMT

I cannot use it in load script as I am handling some user inputs and the chart has to change dynamically on selections.

Regards,

Samir

Re: Finding MIN and MAX

jagan — Mon, 26 Nov 2012 12:10:39 GMT

Hi,

Try this expressions in chart

For Min

=Min(Total <Product_Code> Sales)

For Max

=Max(Total <Product_Code> Sales)

Hope this helps you.

Regards,

Jagan.

Re: Finding MIN and MAX

Mon, 26 Nov 2012 12:33:53 GMT

Hi,

Thanks for the reply, however I am not getting correct results in my actual report (which is different than the example above). In some places I am getting 0 values.

Regards,

Samir

Re: Finding MIN and MAX

perumal_41 — Mon, 26 Nov 2012 12:42:19 GMT

Hi,

Please upload sample application for reference

Re: Finding MIN and MAX

tanelry — Mon, 26 Nov 2012 14:59:36 GMT

You need to aggregate sum of sales over the report dimensions

before picking the minimum / maximum.

This should work:

Min(total<Product_Code> aggr(sum(Sales),Product_Code,Customer_Code,Region))