Grouping in the chart - Aggr function?

Thu, 19 Jan 2012 21:21:56 GMT

Hello fellow QVers,

Can I get some advice here. I think I am going crazy trying to create the expression/formula in making a chart.

Background info

I have one table containing 6 fields:

PackageID

SupplierName
PackageType
ProductName

DateChecked
Quality

There are only three types of values for the field Quality- Excellent, Acceptable, Unacceptable.

My Goal

Make a straight table chart with the following dimensions/expressions.

Dimension: a cyclic group containing these fields - SupplierName, PackageType, ProductName
Expressions: count of PackageID's that are Excellent, Acceptable, and Unacceptable

The Challenge

For every PackageID that I am counting, I only want to know the Quality value of the latest DateChecked.

So I want to group my data by PackageID and get the Quality value for DateChecked = MAX(DateChecked). My expression would be something like this to get the count for 'Excellent' packages:

count (distinct if( Quality = 'Excellent', PackageID ) )

But since there are many combinations of the 3 fields in the cyclic group (SupplierName, PackageType, ProductName) for each PackageID, there can be many different Quality and DateChecked values.

How I want to group

When the dimension is SupplierName, I want my table to be grouped by 'PackageID, SupplierName' to retrieve MAX(DateChecked) and the Quality on that date.

When the dimension is PackageType, I want my table to be grouped by 'PackageID, PackageType' to retrieve MAX(DateChecked) and the Quality on that date.

When the dimension is ProductName, I want my table to be grouped by 'PackageID, ProductName' to retrieve MAX(DateChecked) and the Quality on that date.

Help is greatly appreciated!! I tried using the aggr function but it is totally not working the way I'd want it to.

count( { <DateChecked= {"=aggr( max(DateChecked), CyclicGroupName, PackageID)"}>} distinct if( Quality ='Excellent', PackageID))

topic Grouping in the chart - Aggr function? in QlikView

Grouping in the chart - Aggr function?