Count values based on other columns too

[#1]

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

[swuehl]

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

[#3]

Thank you Stefan, this is the formula I needed.

[#4]

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

[swuehl]

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.

[#6]

Thank you Mark, it is working perfectly. It does everithing I needed.

Thank you again,

Best regards,

Nandor

[#7]

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

[swuehl · Accepted solution]

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.

[abhi1693r]

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

[A7R3]

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