Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
leale1997
Contributor III
Contributor III

How to find Min and Max Values by Group

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)

Labels (2)
5 Replies
dplr-rn
Partner - Master III
Partner - Master III

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

 

leale1997
Contributor III
Contributor III
Author

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?

 

dplr-rn
Partner - Master III
Partner - Master III

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

leale1997
Contributor III
Contributor III
Author

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.

dplr-rn
Partner - Master III
Partner - Master III

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