topic Re: Count values based on other columns too in QlikView

Count values based on other columns too

Sun, 08 Jan 2012 21:45:03 GMT

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

Count values based on other columns too

swuehl — Sun, 08 Jan 2012 22:33:06 GMT

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

Tue, 17 Jan 2012 21:00:23 GMT

Thank you Stefan, this is the formula I needed.

Re: Count values based on other columns too

Tue, 17 Jan 2012 21:19:07 GMT

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

swuehl — Tue, 17 Jan 2012 22:24:54 GMT

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

Wed, 18 Jan 2012 19:34:41 GMT

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

Wed, 18 Jan 2012 20:04:50 GMT

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

swuehl — Wed, 18 Jan 2012 20:52:19 GMT

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.

Re: Count values based on other columns too

abhi1693r — Wed, 26 Jun 2019 16:16:26 GMT

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

Re: Count values based on other columns too

A7R3 — Wed, 18 Dec 2019 05:59:02 GMT

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