8 Replies Latest reply on Sep 9, 2018 3:41 PM by Alex Martino

    Help with filtering and unfaltering

    Alex Martino

      This might be a bit complicated


      In sheet 1:

      There are order Id's, Categories

      There are certain rows that don't have any values in category. These return null.


      In Sheet 2:

      Order Id's can have a number of different "types" of rows (orders, refunds, and customer return fees)

      I'm interested in "customer return fees". I created a formula like this to sum up the customer return fees.

      if [Type] = "FBA Customer Return Fee" and [Fba Fees] < 0 then 1 end


      Because customer return rows have no categories, they show up as Null. However, I would like to count these customer returns fees PER CATEGORY (ignoring customer IDs). In this case I want to see ORIGINAL POLOS = 3 customer return fees.....HOODIES/ORIGINAL POLO = 1 customer return fees

        • 1. Re: Help with filtering and unfaltering
          Okechukwu Ossai

          Hi Alex,


          The solution below works with the sample data provided. Let me know if there are any issues when applied to your actual dataset so I can modify it.

          See attached workbook



          Hope this helps.



          • 2. Re: Help with filtering and unfaltering
            Alex Martino

            Hi Okechukwu Ossai that counts "refunds" not "customer return fees". Let me see if I replace "customer return fees" in your calculation if it will work. I'm guessing that it won't since--as I mentioned--the category tied to customer return fees is null. BUT the customer return fee is tied to order ID, and the order ID has categories for orders and returns.

            • 3. Re: Help with filtering and unfaltering
              Alex Martino

              If you notice in my sheet there are 6 customer return fees and your sheet is showing only 5.


              The result should be:


              Original Polo = 3


              Original Polo/Hooides = 3



              The hard part is that the "customer return fees" are not attached to any category BUT they are attached to orders which DO have categories attached.

              • 4. Re: Help with filtering and unfaltering
                Alex Martino

                I tried this:


                {FIXED [Order Id]: COUNT([Customer Return Fees])}


                The problem is that this seems to duplicate the count when there are more than 1 category per order ID. When there is just one category it appears to be correct. I'll try adding in the category on the fixed but I have the feeling that might eliminate the results since there are 0 customer return fees with category information

                • 5. Re: Help with filtering and unfaltering
                  Okechukwu Ossai

                  Hi Alex,


                  You are correct to say that there are 6 customer returns. There are 3 refunds for  Order ID 111-1819701-2902648 on 3 different dates. 2 of these refunds are for Original Polos and 1 refund for Hoodies. Also, there are 3 refunds for Order ID 113-1192396-7310613 but on the same day (10/01/2015). All 3 refunds are for Original Polos. These refunds are related to SKUs. There is no need to count Customer Return Fees for Category Null, since these returns are recorded as 'Refund' in their respective categories.


                  The solution below should do it. Let me know if you want to modify the formula further. See attached workbook.


                  Hope this helps.




                  • 6. Re: Help with filtering and unfaltering
                    Alex Martino

                    Hi Okechukwu Ossai thanks for your help. I should have specified that in the original data set there are plenty of instances where there is a refund but not an associated customer return fee. So if your logic is assuming that a refund always includes a customer return fee, it does not. Additionally, there can be two refunds for an order ID, and just 1 customer return fee or two refund and no customer return fees etc.


                    I'll take a look at what you have here but it looks like it's still focusing on "refund".


                    At the end of the day, I'm looking for "customer return fees" BY CATEGORY (ignoring customer order ID). For example, order ID's with Original Polos as the order/refund category, how many customer return fees did those categories have?

                    • 7. Re: Help with filtering and unfaltering
                      Okechukwu Ossai

                      Thanks for the explanation. Just one more clarification before I modify the formula


                      How do you want to calculate customer return fee count for Order ID 111-1819701-2902648? Do you want the value of 3 to be repeated for both Hoodies and Original Polos category? Or 1 for Hoodies and 2 for Orignal Polos?

                      • 8. Re: Help with filtering and unfaltering
                        Alex Martino

                        It doesn't matter because the return fees aren't really connected to either of those. Ideally, if you could combine the category to Hoodies/Original Polo and then show 3 that would be the best. Otherwise it doesn't really matter.