13 Replies Latest reply on Jan 21, 2020 3:52 AM by Simon King

    Nested Level of Detail calcs

    Simon King

      Hi All,

      This is my first post to the forum, so please excuse any breaches of etiquette, etc. I have searched through the various posts and questions re. nested LODs and can't find anything that fits my particular problem, so I'm hoping that someone will spot this and show me where I'm going wrong.

       

      The scenario is this. We wish to track new business wins from converted SalesForce opportunities. In the data set, every sales transaction which has resulted from an opportunity is tagged with the opportunity ID. In order to find out in which month the first sale against this opportunity was made, I am using a simple calculation

       

      month(min([Invoice Date])).

       

      I should mention here that the view dimension is Opportunity ID. So this is obviously working fine.

       

      However, in order to qualify as genuinely new business, our rule is that we should not have sold that particular product to that particular customer during the twelve months prior to this sale. But since if we have had any sales of that combination in the prior twelve months, they will *not* have been tagged with the Opportunity ID, in order to sum prior sales of this customer/product combination I need a LOD which ignores the Opportunity ID and acts on the two dimensions Product and Sold to.

       

      The LOD to do this looks like the following:

       

      {fixed [Product], [Sold to] :

      SUM(

      IF

      [Invoice Date] >= dateadd("month",-12, datetrunc("month", [Date of first sale]))

      AND

      [Invoice Date]<datetrunc("month", [Date of First Sale])

      THEN

      [GP]

      ELSE 0

      END

      )

      }

       

      The problem I have is that once the [Date of First Sale] field is nested in this LOD, it ignores the Opportunity ID which it should respect, and so the sum this LOD calc is returning is incorrect.

       

      I get the feeling I need to make the [Date of First Sale] a LOD within the outer LOD and force it to both ignore [Product] and [Sold to] but respect [Opportunity ID], but all attempts to do this so far have failed.

       

      Any pointers on this would be extremely welcome!

       

      Thanks.

        • 1. Re: Nested Level of Detail calcs
          Jim Dehner

          good morning Simon

           

          it is difficult without seeing the TWBX workbook and the data

           

          If I understand the post you want to first find the initial date for and Opportunity ID - to do that you would use an LOD at the opportunity ID level -  {fixed [Opportunity id]:min(invoice date)}  but it is not clear how the Opportunity is related to "...sold that particular product to that particular customer "       If the "opportunity ID " is really related to a customer and a product   then you would want the sold to included in the lod    {fixed [Opportunity id],[sold to]:min(invoice date)}   which in words read for each opportunity ID sold to combination -   find the min invoice date and save it as Date of sale for use later

           

          then  the second calculation as you have written it says for each sold to / product combination  if the invoice date is 12 months before the first date calculated above and the the first date then  GP what ever that is

           

          don't understand how the sale at the sold to product level can precede the min invoice date on the opportunity id -- it implies you have sold the customer some product prior to the first opportunity ID - so what is the opportunity related to - the customer ? the product? or a combination thereof?

           

          the actual nesting of lods is not that difficult but it is depends on an understanding of how LOD s work and interact with context filters

           

          I have an article on my blog that may help'   See it your way: Use LODs to create layer in your data set

           

          it looks like this

           

           

           

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

           

           

          • 2. Re: Nested Level of Detail calcs
            Simon King

            hi Jim,

            Firstly many, many thanks for reading and replying to my question. I take the point that it is much easier to understand if I attach a workbook, but I would need to somehow create a much smaller data set and presumably include the data in the workbook that I post. Apologies, this is one of many things I will have to learn about posting here!

             

            In answer to your questions, we could well have a situation where we had sold a product to a customer in the past and then lost that business. We would then have created an opportunity to go and win the business back. So for example, we may have sold product A to customer B in March 2019, then lost the business. If we won back the business in January 2020, that would show as a converted opportunity. However, because we had the business less than twelve months ago, we would not count that as new. The March 2019 transaction would not be populated with the Opportunity ID, but the January 2020 transaction would be. In order to establish the month of the new win, I am therefore saying "show me the first month we had a transaction which had this Opportunity ID against it." That establishes the month the opportunity was converted. But then when I want to apply the second test, which is "have we sold product A to customer B in the past twelve months?", I need to ignore the Opportunity ID and only look at the product and the customer.

             

            The problem in doing this is that the period I want to look across is based on the the month when the opportunity was converted. I'm finding it impossible to calculate that by including the Opportunity ID, but then ignore the Opportunity ID when summing prior twelve months of sales.

             

            I hope I've written that clearly!

             

            Thanks again for your time and help.

            • 3. Re: Nested Level of Detail calcs
              Simon King

              Also, thank you for sending the link to your blog post on the subject. Sadly, the site was blocked by my company's ultra-rigorous security! I will have a read from my own PC later!

              • 4. Re: Nested Level of Detail calcs
                Jim Dehner

                Please understand - we don't necessarily have a data set the reproduces your problem -

                and we are not necessarily familiar with your internal nomenclature or procedures

                If you post you book with a few records ( 10 -20) that exhibit the issue I will look at it

                 

                thanks

                Jim

                • 5. Re: Nested Level of Detail calcs
                  Simon King

                  Hi Jim,

                  I have mocked up an example of what I'm trying to do in the attached, and have also attached an Excel file which (in Sheet 2) demonstrates how I would do this in Excel.

                   

                  I hope what I've done is in the right format and demonstrates the problem. If you have chance to have a look, that would be great.

                   

                  Many thanks.

                  • 6. Re: Nested Level of Detail calcs
                    Jim Dehner

                    still don't don't understand the business logic but I sorted out the lods -

                     

                    you need your first data to look like this

                     

                    and your 12  month calc to look like this

                     

                    based on the data it returns this

                     

                    best practice is to always work with real dates - not month year in combinations - 

                     

                    JIm

                    If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                    • 7. Re: Nested Level of Detail calcs
                      Simon King

                      Hi Jim,

                      The problem is that as you can see, it is still not returning any values as the sum of the prior 12 months of sales, whereas for three of the opportunities (Opp1, Opp5 and Opp8), it should be, because the customer/item combination these are against have some sales on dates prior to the "date of first sale against opportunity" date. All of the other customer/product combinations do have prior sales so those would not count, but for these three only, the sum of prior sales is zero, and therefore we would count them.

                       

                      (ps. the reason for the strange rule is that we could well have created an opportunity to regain some business that we lost less than 12 months ago, which we don't want to count as new business even though it now has an opportunity ID against it.)

                       

                      Thanks.

                      • 8. Re: Nested Level of Detail calcs
                        Jim Dehner

                        actually I cant see it in the data

                         

                        see the image below it is the raw data  opportunity 5  what is the Opportunity date and what is the date you want to compare agains the start and end of the 12 month window?

                         

                         

                        Jim

                        • 9. Re: Nested Level of Detail calcs
                          Simon King

                          For Opportunity 5, the "date of first sale from opportunity" would be December 2019, so I would then wish to look back and see if there were any sales during the period December 2018-November 2019 against the relevant customer/product combi (ignoring the opportunity ID), in this case H3. For H3 there are no sales before December 2019, so I would flag this as new business.

                           

                          If we then look at Opportunity 4, for example, the "date of first sale from opportunity" would be January 2020, but looking at the customer/product combi (E9) I can see that there are sales in November 2019, so on that basis I would exclude Opp4 from my list of new business wins.

                          • 10. Re: Nested Level of Detail calcs
                            Jim Dehner

                            see if that is what you want

                             

                            it returns this

                             

                             

                            Jim

                            If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                            • 11. Re: Nested Level of Detail calcs
                              Simon King

                              Hi Jim,

                              This is really, really close now, thank you so much! Below is the way I would want to see it, and it is indeed pulling exactly the right opportunities:

                               

                              Now, the one last complication, which I didn't mention as I didn't realize it was significant, is that those prior 12 months sales, whilst they won't be populated with the current opportunity ID, may well have *previous* opportunity IDs associated with them, which I would also wish to ignore. So rather than looking for sales that aren't populated with any opportunity ID (as per your "ISNULL" argument), is there any way of tweaking that so that it picks up all sales that don't match the particular opportunity we're looking at? So for example the prior 12 month sales relating to the customer/product on Opp4 might previously have been associated with Opp333 or something. For the purposes of establishing that Opp4 is new business I would want to include those sales in the prior 12 month check as well as any sales that don't have an opportunity ID against them at all.

                               

                              Many thanks for the progress you've made so far which is already far better than anything I've managed.

                               

                              Very much appreciated.

                              • 12. Re: Nested Level of Detail calcs
                                Jim Dehner

                                you  are going to have to take it on your own at this point

                                sorry

                                Jim

                                If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

                                • 13. Re: Nested Level of Detail calcs
                                  Simon King

                                  Jim,

                                  Apologies it's taken a while to come back on this. I needed to make a few tweaks but I finally have the finished result I was aiming for, and there's no doubt that I only got there with your help, because it was your conceptual change that made it possible. So many, many thanks for your time and assistance.