4 Replies Latest reply on Sep 26, 2017 11:46 AM by Eric Decker

    Fixed Level of Detail by dimension over specific time period

    Eric Decker

      I’m new to fixed level of detail calculations, so I’m hoping there are some experts that can help with my problem.


      I’m trying to isolate a sum of data over a specific 12 month period against a specific dimension so that I can call that sum and manipulate it through other table calculations. I want to use this historic sum and bounce it off current data so that I can apply targets for the coming fiscal year across various dimensions.


      The problem is, I don’t think my Fixed level of detail table calc is doing the right thing. I know I probably need to include my date range in the fixed table calculation, but I’m unsure how to do that accurately.


      Ultimately, the 3 month goals by product group should be as follows (And regardless of what 3 month rolling data I’m using, the goals for each product group should always be these numbers):


      Group 1: 688

      Group 2: 713

      Group 3: 820

      Group 4: 687


      I’ve attached an example workbook that shows what I’m trying to get to with captions describing what’s going on.


      I greatly appreciate your time and advice. Thanks in advance for any and all help!

        • 1. Re: Fixed Level of Detail by dimension over specific time period
          Jim Dehner

          Hi eric

          I'm not certain what you are trying to do but your LOD expression is returning the values you want - see below text table


          The fixed forumla is returning the 3059, 3171,3643, and 3053 values for the product groups - your 3 month formula takes those fixed values and returns the values you want



          The way Fixed works is that it creates permutations of the dimensions that preceed the colon(:) and then aggregates them by the formula the follows the colon


          so in words you formula says "for each product group take the sum of all the unit in the data base (regardles of date) and sum the unit values"


          Now if you want to isolate the fixed expression for a specific year  the fomula owuld include the transaction date


                         {FIXED [Product Group],year([Transaction Date]):Sum([Units])}


          and would return something like this



          or if you filtered for FY


          OK so what - I'm not certain what you are trying to do but that should give you an idea of what the LOD is doing



          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.

          1 of 1 people found this helpful
          • 2. Re: Fixed Level of Detail by dimension over specific time period
            Eric Decker



            Thanks for your response -- I realized a mistake I made in my original request so I'll clarify what I was trying to get at. Before I was including the Fixed calculation for all the data in my data set, and I agree, it was giving me the correct numbers for goals. I apologize, I shouldn't have included September 2017 in that data set.


            With the exclusion of the September 2017 data and only focusing on the FY 16 months, I'm looking to make sure my 3 month unit goals for the different product groups are as follows:


            Group 1: 609

            Group 2: 681

            Group 3: 756

            Group 4: 652


            I took your suggestion of a year filter but made it a little more specific. My new Fixed Aggregate table calculation looks like this:


                 {FIXED [Product Group],[Transaction Date]>=Date('9/1/2016') AND [Transaction Date]<=Date('8/31/2017'):Sum([Units])}


            This does give me the goals I want, but only when I'm looking at data within that date range (which makes sense, because Tableau is doing exactly what I'm telling it to do). But as you can see with my amended formula, as soon as I start using data outside of the date range called out, my formula breaks down and now my goals don't behave the way they should since they are taking into account data points outside the original date range of the Fixed Aggregate formula.




            When using the three month rolling for June - August the new goals (with the new transaction date range applied) is working as expected:


            But when looking at the same charts but with July-September, you'll see how the 3 month goals are now different:


            I understand *why* they are different, I just don't know how to lock in the September 2016-August 2017 values to ensure that only those sums are being called and divided out via my other table calculations. I could duplicate my data source and filter the copied data source to only house those 12 months of information, but I really want to avoid having to do that. I would say maybe using a Lookup function would work, but I'm not sure if that's the best solution since the historic data won't necessarily be included in the viz.


            With this new information, did I better highlight where my goal calculations are falling down when stepping into the new fiscal year??


            Thanks so much for your reply!

            • 3. Re: Fixed Level of Detail by dimension over specific time period
              Ivan Young

              Hey Eric,

              I didn't have a chance to play with your workbook but I think the following might work for you.  Give it a try and let me know if it works.




              { FIXED [Product Group] : SUM( {FIXED [Product Group],[Transaction Date]>=Date('9/1/2016') AND [Transaction Date]<=Date('8/31/2017'):Sum([Units])}) }

              1 of 1 people found this helpful
              • 4. Re: Fixed Level of Detail by dimension over specific time period
                Eric Decker



                Yep, this is exactly what I needed. I didn't think to use a nested fixed statement -- I'll have to keep that calculation in my back pocket for reports to come! Thank you so much for your input!