Hi,
I have the situation similar to the original posting.
https://community.qlik.com/t5/New-to-QlikView/Count-values-based-on-other-columns-too/m-p/258995
The following expression help to count correctly.
=sum( aggr(count(DISTINCT [Final product batch number]), Year, Month, [Product name]))
Could someone help to explain how the logic works here?
Here is the example of the data to look at.
| Year | Month | Product name | raw material name | Final product batch number |
| 2012 | 1 | Product 1 | Raw material 1 | 1 |
| 2012 | 1 | Product 1 | Raw material 2 | 1 |
| 2012 | 1 | Product 1 | Raw material 3 | 1 |
| 2012 | 1 | Product 1 | Raw material 1 | 2 |
| 2012 | 1 | Product 1 | Raw material 2 | 2 |
| 2012 | 1 | Product 1 | Raw material 3 | 2 |
| 2011 | 1 | Product 1 | Raw material 1 | 1 |
| 2011 | 1 | Product 1 | Raw material 2 | 1 |
| 2011 | 1 | Product 1 | Raw material 3 | 1 |
| 2011 | 1 | Product 1 | Raw material 1 | 2 |
| 2011 | 1 | Product 1 | Raw material 2 | 2 |
| 2011 | 1 | Product 1 | Raw material 3 | 2 |
| 2011 | 2 | Product 1 | Raw material 1 | 3 |
| 2011 | 2 | Product 1 | Raw material 2 | 3 |
| 2011 | 2 | Product 1 | Raw material 3 | 3 |
| 2011 | 2 | Product 2 | Raw material 1 | 1 |
| 2011 | 2 | Product 2 | Raw material 2 | 1 |
| 2011 | 2 | Product 2 | Raw material 3 | 1 |
| 2011 | 2 | Product 2 | Raw material 4 | 1 |
| 2011 | 2 | Product 2 | Raw material 5 | 1 |
I would also like to have the count of the [Raw material] with certain [Final product batch number] in the most recent month, by [Product name].
So, If the [Final product batch number] desired is 1, the result for Product 1 should be 3, as the most recent month is 2012+01, and there are 3 counts of Raw material with the batch number = 1.
How should the expression be modified to count this? (I know it should be Max(YearMonth) or something like it. I just can't figure out the correct syntax, probably due my lack of understanding on how the original expression works.)
Thanks in advance,
Ernest
