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

    Count values based on other columns too

      I have a list like this

       

      Product nameraw material nameFinal product batch number
      Product 1Raw material 11
      Product 1Raw material 21
      Product 1Raw material 31
      Product 2Raw material 11
      Product 2Raw material 21
      Product 2Raw material 31
      Product 2Raw material 41
      Product 2Raw material 51
      Product 3Raw material 11
      Product 3Raw material 21
      Product 3Raw material 31

       

       

      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
          Stefan Wühl

          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

                   

                   

                  YearMonthProduct nameraw material  nameFinal product  batch number
                  20121Product 1Raw material 11
                  20121Product 1Raw material 21
                  20121Product 1Raw material 31
                  20121Product 1Raw material 12
                  20121Product 1Raw material 22
                  20121Product 1Raw material 32
                  20111Product 1Raw material 11
                  20111Product 1Raw material 21
                  20111Product 1Raw material 31
                  20111Product 1Raw material 12
                  20111Product 1Raw material 22
                  20111Product 1Raw material 32
                  20112Product 1Raw material 13
                  20112Product 1Raw material 23
                  20112Product 1Raw material 33
                  20112Product 2Raw material 11
                  20112Product 2Raw material 21
                  20112Product 2Raw material 31
                  20112Product 2Raw material 41
                  20112Product 2Raw material 51
                  20114Product 3Raw material 11
                  20114Product 3Raw material 21
                  20114Product 3Raw material 31
                  20115Product 3Raw material 12
                  20115Product 3Raw material 22
                  20115Product 3Raw material 32
                    • Count values based on other columns too
                      Stefan Wühl

                      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

                  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