3 Replies Latest reply on Sep 26, 2016 5:12 AM by Alastair Young

    Can my LOD trump a calculated field that filters?

    Samuel Gee

      Hi,


      I have 2 formats, A and B. I have a workbook that is all about format A. So all the pills are hard coded to look for it (which I am swiftly realising was bad practice).


      Eg: The pill that works out the most recent week's and prior week's sales - called [Weeks] - says:

       

      If [Format] = "A" and [Week] = {FIXED: MAX([Week])} then "Recent Week" ELSEIF

      If [Format] = "A" and [Week] = {FIXED: MAX([Week])}-7 then "Prior Week" else null END

       

      And I just drop that into Columns and [Sales] into the view and there you go.

       

      What I'd like to do now though, unexpectedly, is work out in that same view what proportion of total sales A was in each week.

       

      So I have another measure:

       

      SUM([Sales])/SUM({ EXCLUDE [Format]:SUM([Sales])})

       

      I was hoping that the EXCLUDE would ignore the coding in the [Weeks] pill, which specifically only shows format A sales, but it's not. I can't seem to get Format B sales in the pill by any means.

       

      Is there a way around this without recoding all the hard coded pills?

       

      Thank you,

       

      Sam

        • 1. Re: Can my LOD trump a calculated field that filters?
          Alastair Young

          Hi Samuel,

           

          I am assuming the figures represented in the following are correct. So you are essentially trying to display Format A , Prior Week as 50% and Recent week as 66%.

           

          I know this is not the correct structure but I want to ensure the values are correct before the shaping of the output.

           

          Also, Is the output you expect required to be in the same format as your first worksheet?  Format in the Rows, and the Columns also?

           

          Thanks

          Al

           

          • 2. Re: Can my LOD trump a calculated field that filters?
            Samuel Gee

            Hi Al,

             

            Thank you, and yes those figures are correct.

             

            The output would probably be broadly as in sheet 1, though I'd likely just have the Weeks pill in columns and the measure in the viz, rows empty.

             

            Thanks,

             

            Sam

            • 3. Re: Can my LOD trump a calculated field that filters?
              Alastair Young

              Samuel,

               

              Firstly I would change your Weeks calculation to the following

               

              IF [Format] = "A" and [Week] = { FIXED :MAX([Week])} then "Recent Week" ELSEIF

              [Format] = "A" and [Week] = { FIXED :MAX([Week])}-7 then "Prior Week" ELSEIF

              [Format] = "B" and [Week] = { FIXED :MAX([Week])} then "Recent Week" ELSEIF

              [Format] = "B" and [Week] = { FIXED :MAX([Week])}-7 then "Prior Week" ELSE

              null END

              I have done this as presently your Week calc does not assign Format B and week referencing. This is crutial to get the correct percentage.

               

              What I have done is add another Sales measure to the crosstab, assigned this a table calculation of 'Percentage of Total' as well as changing the Compute Using to Table down.  This provides the output to sheet 4.

               

              If you wish to remove Format B you can simply right click and hide this.

               

              If you do anything else further with this, you need to ensure that the compute using feature is correctly assigned.

               

              I have attached the updated workbook for you.

               

              Thanks

              Al