Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a list like this
Product name | raw material name | Final product batch number |
Product 1 | Raw material 1 | 1 |
Product 1 | Raw material 2 | 1 |
Product 1 | Raw material 3 | 1 |
Product 2 | Raw material 1 | 1 |
Product 2 | Raw material 2 | 1 |
Product 2 | Raw material 3 | 1 |
Product 2 | Raw material 4 | 1 |
Product 2 | Raw material 5 | 1 |
Product 3 | Raw material 1 | 1 |
Product 3 | Raw material 2 | 1 |
Product 3 | Raw material 3 | 1 |
If I use Count (DISTINCT [Final product batch number]), the function will return 1.
If I use Count ([Final product batch number]), the function returnes 11.
The corect answer would be 3, because batch 1 is the batch number of the Product, the raw materials were marked with batch 1 just to group it to the coresponding final product batch.
We have 3 final products with the same batch number, I would need to count them, for every month.
Please help me to count the batches taking in consideration the Product name too.
Thanks,
Nandi
If you want an aggregation that is not influenced by selection, use e.g.
count({1} [Final product batch number])
{1} is a set expression, if you are not familiar with that, please start with the Help page.
Maybe I haven't fully understood your requirement, but I think it can be done similar to
=sum( aggr(count(DISTINCT [Final product batch number]), [Product name]))
Your case seems to be quite special with all batch numbers equal zero, if above expression is not what you are looking for, I think we can improve on that quite easily with a bit more information.
Regards,
Stefan
Thank you Stefan, this is the formula I needed.
Mark, I just realised that the formula is not working if the month is added.
I attached to this post the excell file and the QlikView file too. I have a free version, I hope you will be able to open it.
I will also post a copy of the table that is in the excel file on this post.
I am trying to have a dig down barchart, from year to month and than to see the number of batches for the products in any month I choose.
Thank you,
Nandor
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 |
2011 | 4 | Product 3 | Raw material 1 | 1 |
2011 | 4 | Product 3 | Raw material 2 | 1 |
2011 | 4 | Product 3 | Raw material 3 | 1 |
2011 | 5 | Product 3 | Raw material 1 | 2 |
2011 | 5 | Product 3 | Raw material 2 | 2 |
2011 | 5 | Product 3 | Raw material 3 | 2 |
I am bit unsure how you want to count the batch numbers in the case of aggregated dimensions, like Month --> Year.
Maybe your solution is quite near, you could try just adding your dimensions to the aggr() function dimension list:
=sum( aggr(count(DISTINCT [Final product batch number]), Year, Month, [Product name]))
If this is not what you want, please give an example of your expected result per dimension / level of drill down hierarchie and please describe once more how you derive your results from the source table.
Thank you Mark, it is working perfectly. It does everithing I needed.
Thank you again,
Best regards,
Nandor
Dear Mark,
I have an another issue in an another table:
After I have the number of batches calculated for every month I need to calculate the labor costs per batch.
For this I need to devide the labor costs for one month to the number of batches.
The problem is that when I select a product, the labor costs are devided with the number of batces of the selected product. For example if I have 10 batches in one month but I selected a product that was produced only one batch that month, the labor costs are devided only to one.
The solution would be a formula that is not influenced by selections, I need the total number of batches per month no matter what selections occur.
Could you help me out with this one too?
Thanks,
Nandor
If you want an aggregation that is not influenced by selection, use e.g.
count({1} [Final product batch number])
{1} is a set expression, if you are not familiar with that, please start with the Help page.
Hi Stephan,
I had a similar issue. So I have two columns, PART AND CUBES, with one to many relationship.(one PART can have many CUBES or a PART can have no CUBE).
Scenario: If I select 3 PARTS which have 2 CUBES, wherein 2 PARTS have 2 CUBES and 1 PART has no CUBE. I want a count of all the CUBES plus any PART which has no CUBES.
So the count should be 2+1=3.
How can I implement this?
I tried count(PART) + count(if(IsNull(CUBE),count(PART),0)) , but it didn't work.
@swuehl
Regards,
Abhishek
Hi,
I have the situation similar to the original posting and 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?
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