9 Replies Latest reply on Jul 2, 2018 3:05 PM by Ken Flerlage

    Fixed LOD isn't working as expected

    dinesh punnam

      Hello All,

       

       

      I have a worksheet with fixed LOD and the filter is changing the values

       

      Below : Gross Revenue NC is FIXED LOD calc: region filter should not be effecting below number numbers

       

       

      What ever the region is the gross revenue should show same numbers - please let me know

       

      Thanks,

      Dinesh

        • 1. Re: Fixed LOD isn't working as expected
          Deepak Rai

          Thanks

          Deepak

          1 of 1 people found this helpful
          • 2. Re: Fixed LOD isn't working as expected
            dinesh punnam

            Thanks Deepak for your time and input!!

             

            Let me put it this way

             

            I am trying to achieve gross revenue same for all regions so that I can calculate the CPM (Gross Revenue/Impresssions * 1000) like below screen shot

             

             

             

            From our work sheet 4 please we are getting different gross revenue even though I use "FIXED LOD" - can we achieve same Gross revenue so that we

            can get the below CPM's matching to above screen shot,

             

             

             

            Thanks,

            Dinesh

            • 3. Re: Fixed LOD isn't working as expected
              Ken Flerlage

              Your LOD is not set up correctly. You do not need all the dimensions in the LOD that you have currently. Change it to the following:

               

              {FIXED [SalesOfficeId]:SUM([Gross Rev])}

               

              Then, right-click on the Target_Audience and YEAR(Transaction_Date (Years)) pills in the Filters area and select "Add to Context". This will ensure that the filters are applied before the LOD is calculated.

               

              See below. The column on the right is a new version of your Gross Revenue LOD that I created. You'll notice that the number is 12.42 M and not 10.77 M. 10.77 M is not the right sum--the grand total is not accurate due to the incorrect LOD.

               

               

              From here, you'll need to make adjustments to the rest of your calcs that use this LOD because it is no longer an aggregate. The changes should be relatively simple though.

               

              Please take a look at the attached workbook. If you have any further questions, let us know. Otherwise, please be sure to mark this answer as helpful or the "correct answer" so others may learn from it in the future. Thanks!

              1 of 1 people found this helpful
              • 4. Re: Fixed LOD isn't working as expected
                dinesh punnam

                Thanks KEN,

                 

                 

                Your LOD is not set up correctly. You do not need all the dimensions in the LOD that you have currently. Change it to the following:

                our data set up is based on the below query so I have to put all those dimensions to get that number

                 

                (

                select Transaction_Date, YearMonth, GregorianMonthNum, GregorianMonthCode, GregorianQuarterCode, GregorianYear, GregorianYearAlias

                , SalesOfficeId, CombinedOfficeDesc, SellingPatternId, SellingPatternDesc, PrimeNonPrimeFlag, NetworkId, NetworkCode, NetworkDesc

                , NetworkGroup, ClientId, Adv_Name, Adv_White_Label, Adv_Gr_Industry, Adv_Type_of_Bundle, Adv_Local_Regional, Adv_New_Renew_PY

                --, RegionId, Region, TargetId, Target_Audience

                , Adv_Status_vs_Prev_Year--, Impressions_Std_Unit_Based

                , AVG(GrossRev) as GrossRev, AVG(StandardUnits) as StandardUnits, SUM(Impressions_Std_Unit_Based) as Impressions_Std_Unit_Based

                from RPT_PricingBuy

                 

                 

                 

                 

                I tried Sum of test --> { EXCLUDE [Region]:[Gross Revenue NC]} to get 10.77 across the regions and it WORKED and CPM (Copy 2 are correct numbers ) but i am not able to use region filter to toggle those numbers

                 

                Gross revenue number shouldn't be effected by region and impressions should effect it so that the CPM calc will be right . please help!!

                 

                 

                 

                 

                 

                below is from our SSAS Cubes and that's the number we are trying to match

                n

                \

                • 5. Re: Fixed LOD isn't working as expected
                  Ken Flerlage

                  OK, I think I understand what you're trying to do, though I can't say I really understand why you want to take these averages at all these levels. But, putting that aside, give the following a try:

                   

                  Change your test LOD to be the following:

                   

                  {FIXED [SalesOfficeId]:[Gross Revenue NC]}

                   

                  You also need to right-click on the Year and Target_Audience filters and add it to context so that filter will be applied before the LODs.

                   

                  After this is done, you should be able to filter on region without it impacting the result.

                   

                  • 6. Re: Fixed LOD isn't working as expected
                    dinesh punnam

                    Thanks much Ken - Appreciate your time!!   The data set is not appropriate , that's true but will write about that i details ASAP.

                    • 7. Re: Fixed LOD isn't working as expected
                      Ken Flerlage

                      Did the solution work for you?

                      • 8. Re: Fixed LOD isn't working as expected
                        dinesh punnam

                        Ken & Deepak

                         

                        Your solution helped!! One quick question - can you let me know if this is possible or not in Tableau >?

                         

                         

                        I have a db like below and assigned fixed value to all the region for each sales office - For Brazil& Brasil intersection i only have one match - is there nay way  i can populate all other regions with 74457 947 number ??

                         

                        You guys are awesomeee - thanks for your time.

                         

                        Dinesh

                        • 9. Re: Fixed LOD isn't working as expected
                          Ken Flerlage

                          That would be pretty tricky--Tableau, by default, will only show data that is in your data set. In order to do this, however, you could get a unique list of each region, then do a left join to your actual data set. This will ensure that you have each region, even if it's not in your data. It's a bit hacky but would work.