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:
- SupplierName
- PackageType
- ProductName
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))
