7 Replies Latest reply: Jan 18, 2012 3:52 PM by Stefan Wühl

# Count values based on other columns too

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.

Thanks,

Nandi

• ###### Count values based on other columns too

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

• ###### Count values based on other columns too

Thank you Stefan, this is the formula I needed.

• ###### Re: Count values based on other columns too

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
• ###### Count values based on other columns too

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.

• ###### Re: Count values based on other columns too

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

Thank you again,

Best regards,

Nandor

• ###### Re: Count values based on other columns too

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

• ###### Count values based on other columns too

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.