Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hey,
I created additional field in the load:
Year * 12 + Month as YearMonthNum
which I use in the calc later on:
count({<YearMonthNum = {"$(=Max(YearMonthNum))"}, [Final product batch number] = {'1'}>}[raw material name])
Hey,
I created additional field in the load:
Year * 12 + Month as YearMonthNum
which I use in the calc later on:
count({<YearMonthNum = {"$(=Max(YearMonthNum))"}, [Final product batch number] = {'1'}>}[raw material name])