1 2 Previous Next 18 Replies Latest reply on Apr 19, 2017 8:25 AM by Okechukwu Ossai

    LOD issue with data hierachy

    Ian Barton

      Hi,

       

      I have a revenue table attached to a hierarchy of 5 levels. Country> Business Unit> Franchise> Brand> Indication. I'm trying to find the maximum sales at each level but I'm having real trouble writing a calculated field.

      The LOD works for

       

      Brand using the following formula but this only works sometimes

      { INCLUDE [Indication]: MAX([Value])}

       

      But for Franchise and Business Unit, I'm getting some numbers that work and some that don't i.e Lung and Breast. Could anyone advise what Im doing wrong please? Its driving me mad!

      Ive got 2 sheets per hierachy. One with the raw data and yellow sheet contains the LOD calc.

       

      Thank you!!

        • 1. Re: LOD issue with data hierachy
          Sharad Adhikari

          Hi Ian,

           

          I would rather use FIXED and use dimension that I wanted to find the maximum value. So for example, if I wanted to get the maximum value for Brand, the formula would look like,

           

          { FIXED [Brand]: MAX([Value])}

           

          Could you give it a try?

           

          BR

          Sharad

          • 2. Re: LOD issue with data hierachy
            Deepak Rai

            Hi Ian,

            I used LODs for Individual Views. Check Screenshot and attached. I also converted your Year Calculation to Date.

            Hope it Helps!!!

            Thanks

            Deepak

            • 3. Re: LOD issue with data hierachy
              Okechukwu Ossai

              Hi Ian,

               

              From your question, I understand that you want to find Max Sales at each of the 5 levels. You can do this using LOD expressions adding each level progressively as you move down the hierarchy.

               

              Starting from the topmost hierarchy;

               

              1. [Max Year Value Country]

              { FIXED [Country]: MAX([Value])}

               

              2. [Max Year Value BU]

              {FIXED [Country], [BU]: MAX([Value])}

               

              3. [Max Year Value Franchise]

              { FIXED [Country], [BU], [Franchise]: MAX([Value])}

               

              4. [Max Year Value Brand]

              { FIXED [Country], [BU], [Franchise], [Brand]: MAX([Value])}

               

              5. [Max Year Value Indication]

              { FIXED [Country], [BU], [Franchise], [Brand], [Indication]: MAX([Value])}

               

              I have also calculated the Max Year for each level. I have set the default aggregation method for both Max Year and Max Year Sales as Minimum, to avoid the issue you had previously when multiple years have the same Sales value.

               

              See attached workbook and the edit I made to each worksheet. Hope this helps.

               

              Ossai

              • 4. Re: LOD issue with data hierachy
                Ian Barton

                Okechukwu Ossai,

                 

                Ive looked at your formulas and Max sales and its not quite matching the raw data. I've attached 3 screenshots at brand/ franchise/ BU. I think that the formula is pulling data at the lowest level - indication. Could you help me understand whats going on. I've attached 3 screenshots.

                 

                 

                Thanks!

                • 5. Re: LOD issue with data hierachy
                  Ian Barton

                  Essentially, I'm trying to create something like this.

                  • 6. Re: LOD issue with data hierachy
                    Okechukwu Ossai

                    Hi Ian,

                     

                    I can see where the problem is coming from. There are 2 issues;

                     

                    First, your screenshots are displaying SUM(Value). SUM(Value) and Max Year Value are not identical and so cannot be compared. This is because SUM(Value) sums all the values for all the years for a particular brand and its indicators. If you want to compare the result to Max Year Value, then change the aggregation from SUM(Value) to MAX(Value). See Sheet 9, Sheet 10 and Sheet 11 of attached workbook for the correct aggregation.

                     

                    For example, Prd 1 in 2018, sums the values for Prd 1 2L and Prd 1 1L; 8,489,838 + 35,897,597 = 44,387,435.  Prd 2 has just 1 indicator so returns that value only.

                     

                    Second, I have also updated the codes after looking at the hierarchy again.

                     

                    1. [Max Year Value Country]

                    { FIXED [Country]: MAX([Value])}

                     

                    2. [Max Year Value BU]

                    {FIXED [Country], [BU]: MAX([Value])}

                     

                    3. [Max Year Value Brand]

                    { FIXED [Country], [BU], [Brand]: MAX([Value])}

                    Returns maximum sales for each brand irrespective of the franchise.

                     

                    4. [Max Year Value Franchise]

                    { FIXED [Country], [BU], [Franchise]: MAX([Value])}

                    Returns maximum sales for each franchise irrespective of the brand.

                     

                    5. [Max Year Value Indication]

                    { FIXED [Country], [BU], [Franchise], [Brand], [Indication]: MAX([Value])}

                     

                    See attached workbook.

                     

                     

                     

                     

                     

                     

                     

                    • 7. Re: LOD issue with data hierachy
                      Okechukwu Ossai

                      Hi Ian,

                       

                      Essentially, I'm trying to create something like this.

                       

                      The KPI Sparkline image you attached cannot be recreated using the workbook you attached. There are other new calculated fields like PYS Franchise.......

                       

                      I can't help much by just looking at the image. You need to attach the related workbook.

                       

                      Ossai

                      • 8. Re: LOD issue with data hierachy
                        Ian Barton

                        Hi,

                        Ah i see what you've done. Maybe this my fault for not explaining well enough. What I'm trying to find is the Maximum value per year for each brand (summing all indications below). For example in the screenshots below,

                        a) for Prd 1, Id like to show the Max value of the sum of all indications under Prd 1 which is 44.3M in 2018. Maybe the calculation something like Max(sum(Brand))? (Brand Image)

                        b) for Franchise  Id like to show the Max value of the sum of all indications, products under Lung 117.3M in 2023 (Franchise image)

                        • 9. Re: LOD issue with data hierachy
                          Ian Barton

                          Hi,

                           

                          Ive just seen your latest post. Many thanks! Here's the workbook with the LOD summing at brand and franchise for sparkline use. I've created sparklines i think the issue is created by the Max formulas problem. The screenshot is the view im looking for - One row of charts at level

                           

                          Thank you

                          • 10. Re: LOD issue with data hierachy
                            Okechukwu Ossai

                            Hi Ian,

                             

                            I'd like to know clearly what you want to show on the sparklines because the fields displayed on the image are different from those on the chart in your attached workbook. For example are the bars sum sales values for each franchise per year or are the max sales values?

                             

                            I've got a working solution for you but would want to provide a final answer without further going back and forth. Could you please confirm that the fields highlighted in yellow are what you are looking for? If they are not, can you give me a list of all the dimensions and measures you want on the chart please.

                             

                            • 11. Re: LOD issue with data hierachy
                              Ian Barton

                              Hi,

                               

                              Thank you. Yes the fields you've highlighted are the correct fields. Many thanks for your help.

                               

                              Kind Regards,

                               

                              Ian

                              • 12. Re: LOD issue with data hierachy
                                Okechukwu Ossai

                                Hi Ian,

                                 

                                You are correct about the Null coming from Max Year calculation.

                                 

                                To resolve this I converted Max Year Value Brand and Max Year Value Franchise as dimensions. This will allow them to be used in an LOD expression. I created new  Max Year fields also and updated the Value Brand and Value Franchise fields. See the screenshots below for updated fields circled in red.

                                 

                                All the details are in the attached workbook.

                                 

                                Hope this helps.

                                 

                                Ossai

                                 

                                • 13. Re: LOD issue with data hierachy
                                  Ian Barton

                                  Hi,

                                   

                                  Thanks you so much for all your help on this!!

                                  The only discrepancy is that the Max Year Value Brand should equal the maximum value shown in the chart. I've attached the screen shot as this makes it clearer. Are you able to amend the Max Year Value Brand calculation. I can then do the rest

                                  In all cases the Max Year Value Brand and Max Year Value Franchise seem to calculate the "Max Value" at a lower level to what they should be. The Max Year Value Brand should equal the Max value in the chart.

                                   

                                  Thanks for all your help and hard work

                                  • 14. Re: LOD issue with data hierachy
                                    Okechukwu Ossai

                                    Hi Ian,

                                     

                                    That's not a problem. It's taken some time but I believe we are finally getting there. Table calculation functions were used for codes #2 and #3 below. That was the only way possible to make this to work. This is because LOD expressions and ATTR cannot be mixed together. See the codes and attached workbook below.

                                     

                                    Max Revenue per Brand

                                     

                                    1. [Value Brand]

                                    { FIXED [Country], [BU], [Brand], [year]: SUM([Value])}

                                     

                                    2. [Max Sales Brand]

                                    WINDOW_MAX(SUM([Value Brand]))

                                     

                                    3. [Max Sales Year Brand]

                                    WINDOW_MAX(IF SUM([Value]) = [Max Sales Brand] THEN ATTR(YEAR([Date])) END)

                                     

                                    Max Revenue per Franchise

                                     

                                    1. [Value Franchise]

                                    { FIXED [Country], [BU], [Franchise], [year]: SUM([Value])}

                                     

                                    2. [Max Sales Franchise]

                                    WINDOW_MAX(SUM([Value Franchise]))

                                     

                                    3. [Max Sales Year Franchise]

                                    WINDOW_MAX(IF SUM([Value]) = [Max Sales Franchise] THEN ATTR(YEAR([Date])) END)

                                     

                                    To make this to work, click on [Max Sales Franchise] and [Max Sales Year Franchise] on the rows shelf and set Compute using to "Table (across)". Do the same for the Brand chart.

                                     

                                    Hope this helps.

                                     

                                    Ossai

                                     

                                     

                                    1 of 1 people found this helpful
                                    1 2 Previous Next