topic Re: How to find Min and Max Values by Group in QlikView

How to find Min and Max Values by Group

leale1997 — Sat, 16 Nov 2024 00:48:42 GMT

I would like to find the Min and Max value of a column based on the grouping it belongs to. Grouping is designated by a combination of the values in two columns.

The data looks like this:

Category Sub Category Days

A 1 10

A 1 20

A 1 30

So for Category A,1 the Min is 10 and the Max is 30

I attached a data sample for you.

Thanks in advance for any assistance :0)

Re: How to find Min and Max Values by Group

dplr-rn — Mon, 20 Apr 2020 15:14:57 GMT

explain what you are trying to achive in more detail. because context matters.

e.g. if you are doing on a chart its simple as adding category and sub category as dimension and give measures min and max

if you want it in someway in the data model you will need to resident load and group by category and sub category

Re: How to find Min and Max Values by Group

leale1997 — Mon, 20 Apr 2020 15:24:37 GMT

Good question. As I give it more thought I actually have three groupings or columns to group by.

Category Sub Category1 Sub Category2 Days

A 1 a 10

A 1 b 20

A 1 b 30

A 1 a 5

My initial thoughts were to incorporate a chart or straight table that might look something like this:

Product - Range

A,1,a - 5 - 10

A,1,b - 20 - 30

This would require the 'grouping' with Min&Max to only be in the chart or graph.

Now that you have me thinking about it more. It might be easier to include the grouping in the data model? It might also allow me more flexibility with buttons for drill downs and other analytics.

Can I add it to the data model?

Re: How to find Min and Max Values by Group

dplr-rn — Mon, 20 Apr 2020 15:27:41 GMT

you could add it to data model but it may reduce flexibility.

i would say do it chart.

unless there is a special scenario you need

Re: How to find Min and Max Values by Group

leale1997 — Mon, 20 Apr 2020 15:39:34 GMT

ok. So how would I include the grouping with Min and Max values in a chart? I attached a better data set for you that has he second category added in.

Also note that what am initially trying to do is just group them in a table. With 'Brand' and 'TA' being rows and Market being columns. Might actually be easier in a pivot table.

Re: How to find Min and Max Values by Group

dplr-rn — Mon, 20 Apr 2020 15:45:05 GMT

like i mentioned in the initial reply. when you give aggregations in a measure (sum, min, max avg ) it is automatically grouped according to the dimensions given.

so in you case you give Brand, TA, Market as dimensions. a measure like Min(Days) will give you minimum grouped by those dimension values. similary max(Days) will give you maximum groups by those dimensions