5 Replies Latest reply on Feb 21, 2019 1:45 AM by Alex Martino

    Difficult text box question

    Alex Martino

      I have order IDs with information about a product sales. Then later, I have customer return fees that lack most information BUT still attach to the order ID. This is an example.



      11/1/2017, ID 22, Category Shirts

      11/1/2017, ID 22, Category Pants


      12/15/2017, ID 22, Category NULL



      I want to look at the refund information but still have the category information. I created this and thought I could filter for value in customer returns fee to make the table "cleaner" (eliminating the non refund entries) but when I do that it eliminates the categories. How can I get around this?


      Screen Shot 2018-12-24 at 9.39.02 PM.png

        • 1. Re: Difficult text box question
          amar savale

          Hi Alex,

          Can you please share your .xls or .twbx file for more understanding.





          Amar D. Savale

          • 2. Re: Difficult text box question
            Paul Wachtler

            Hi Alex,


            You need some calculations to pare your data down to what you want.  Just to confirm, it sounds like you want to see the data like this:


            Order Id .         Category .            Refund



            If that's what you want to see,


            1) Keep Order Id on your rows shelf like you have it.


            2) For Category, create a calculated field to return the category for the order Id, regardless of blank rows:

            {fixed [Order Id]: max([Category])}


            3) Place that on your rows shelf (make sure it's discrete)


            4) I can't tell what your refund column field is called, but for example, if it's called [customer return fees] then take the sum of that sum([customer return fees]) and place that on your Text mark card.


            That should do what you're looking for.  If you need help putting that together, share your workbook and I'll take a look.




            1 of 1 people found this helpful
            • 3. Re: Difficult text box question
              Alex Martino

              Great that worked. Is there a way that I can take the order ID off the view and see the totals by category? When I take out order ID it changes the results. Ideally I would like to see bar charts by category. I reversed the order of category and ID and then I can at least see totals but I don't really need the order ID detail. Thanks!

              • 4. Re: Difficult text box question
                Paul Wachtler

                If you use an LOD expression for the refund calculation then you should be able to sum at the category level.


                First, take this calculation for calculating the refund amount per Order ID:

                {fixed [Order Id]: sum([customer return fees])}


                Then place Category on your rows shelf, place another copy on your filters shelf and filter out the null values.  Then place that LOD calculation on your text mark.  That should give you refunds by category.

                1 of 1 people found this helpful
                • 5. Re: Difficult text box question
                  Alex Martino

                  Hi Paul Wachtler. Another question for you, what happens when there are multiple categories per order ID?


                  Unfortunately, my data doesn't specify the category that a return amount is related to when there are multiple categories.


                  For example:


                  Order ID 1. Category Polo

                  Order ID 1.  Category Jean

                  Order ID 1. Category Blouse


                  There were three items purchased (each a transaction) but just one had a customer return fee. So you'll see the fee and the order ID but not the specific category. How does your formula handle this?