7 Replies Latest reply on Aug 17, 2016 10:33 AM by Ivan Young

    LOD by parent and category

    Elena Lisovskaya

      Hi All,

      I'm looking for solution for two problems

      I have list of customers (a1,a2,a3, b1,b2.. ) witch products they bought, each of them has parent(A, B)

       

      two question I have:

      1. if I select one customer (a1)  how  I can combine in  one table summary by product per customer(a1) and per Parent(A).

      I created LOD for parent :  {FIXED [Parent],[Product]:sum([Amount])} but it shows summary by parent only  products which selected customer has

       

      2. How I can get list of all customers (a1-a6) from family (A) if I select only one customer (a1)

       

      Thank you for help!

       

      attached example of workbook.

        • 1. Re: LOD by parent and category
          Ivan Young

          Hi Elena,

          You will probably need to use a parameter to accomplish your goal.  I'm not exactly sure what your desired output is, particularly for combine in  one table summary by product per customer(a1) and per Parent(A).

           

          Usually looking up a parent from a child record will require a fairly complex table calculation but since your child records have the parent as the first character it won't be necessary.

           

          1.  Create child parameter.

           

          2. Create filter for details worksheet

          [Parent] = LEFT([Child Parameter],1)

           

          3.  Drag to filters on details worksheet and click True. - This will let you lookup all child records with the same parent.

           

          For your other two worksheets (since I'm not sure what your desired output is you may need to modify)

           

          1.  Create filter for summary and summary product worksheets.

          [Child] = [Child Parameter]

           

          2. Drag to filters on both worksheets and click true.

           

          You will then need to modify your dashboard to use the parameter rather than your quick filters.

           

          Attached is a 9.2.4, I'm not sure if you'll be able to open as I had to upgrade the file.  Let me know if you have any questions.

           

          Regards,

          Ivan

           

          • 2. Re: LOD by parent and category
            Elena Lisovskaya

            Ivan,

            thank you for response.

            It was my first approach by parameter.

            but in real data child id is 9 digits and child name up to 50 letters/digits and it is kind of difficult for users to type it in.

            I thought about more user friendly interface when user can type part of customer name (n replace child Id by wild card content search )and set up  dashboard action filter  - if I click on selected child- some sheets will have filter child id and some- parent id.

            not sure if it possible.

             

            Elena

            • 3. Re: LOD by parent and category
              Ivan Young

              Hi Elena,

              Most of my users have a hard time typing more than 2 characters accurately so I never use free text parameters.  If possible you should always have your users select parameters from a list.

               

              I'm not entirely clear on your action filter based approach but it sound to me like it would also require a parameter for users to type something into?

               

              Regards,

              Ivan

              • 4. Re: LOD by parent and category
                Elena Lisovskaya

                My customer list  >50,000, therefore  I thought the best way to find right customer is

                1.allow user type part of name ,

                2.then find all customers with this string in the name and generate list.  customer-parent

                3. user select customer from list

                4 dashboard has action filters  and passing child id to selected sheets and parent id to other selected sheets

                 

                Thank you!

                Elena

                • 5. Re: LOD by parent and category
                  Ivan Young

                  Thanks Elena,

                  It's more clear now.  This is possible but will still require a parameter to generate a customer list to use a source filter.  I've attached a twbx.  Take a look at dashboard 2 and let me know if you have any questions.

                   

                  Regards,

                  Ivan

                  • 6. Re: LOD by parent and category
                    Elena Lisovskaya

                    Thank Ivan!

                    it works

                    do you have any suggestion for my second issue-

                    I want to show in one table all products by selected customer and by parent.

                    LOD I created   {FIXED [Parent],[Product]:sum([Amount])} shows only products which selected customer has ("summary product" sheet)  because filter applied.

                    can I exclude action filter - child from LOD calculation or maybe use other function

                     

                    Elena

                    • 7. Re: LOD by parent and category
                      Ivan Young

                      I'm glad it worked.  It was an interesting way of looking up parent records from a child that I hadn't seen or thought of before so thanks for coming up with the idea. 

                       

                      For your second issue I think you would need a parameter to do it all in one worksheet.  An option would be to create two worksheets and place them side by side one being filtered at the child level and another at the parent.  You would need to show empty rows because the child table would have less data than the parent table.  Could you tell me if the screenshot below is what you would expect for child a1.

                       

                       

                       

                       

                      Thanks,

                      Ivan