3 Replies Latest reply on Jun 15, 2017 8:54 AM by Jim Dehner

    Sum of Next 4 Weeks - LOD Expression Help

    Kevin Warren

      Hello,

       

      I have attached the following twbx file with a story explaining my obstacle.

       

      I am trying to display in a table, the sum(next 4 week sales) on the same row, without displaying dates. I think I am looking for an alternative for a table calculation.

       

      Thank you for all your help!

        • 1. Re: Sum of Next 4 Weeks - LOD Expression Help
          Jim Dehner

          Hi Kevin

           

          I've been fighting my way through your workbook - the bad news is LOD expressions require all elements come from a single data source so I don't think that is approach

           

          I think you may be able to come up with a hack that gets you what you want - but I got lost in calculations

          You have created and integer key that you related to the date and year - that is a good way to go

          Where I got lost is the how you determine the Key Range that you want to sum across - it looks like you are trying to use a window-max function to set the start date -

          if that is the cast then it looks like the IF statement

          if attr([Fiscal Week Key]) >[Sales and Dimensions].[Most Recent Week]

          and attr([Fiscal Week Key]) < [Sales and Dimensions].[Most Recent Week]-5

          Then sum([Sales Fcst Qty])

          Else 0 end

           

          Should get you the total you want - but I could not get it to work

          Let me know if this helped at all

          Jim

          1 of 1 people found this helpful
          • 2. Re: Sum of Next 4 Weeks - LOD Expression Help
            Kevin Warren

            Thank you for digging into this and providing me feedback. I was able to find a solution using Custom SQL to aggregate my rows at the needed detail level and create 2 new fields.

             

            avg(sales_fcst_qty) over(partition by prime_item_key order by prime_item_key, [Fact_Customer_Sales_Forecast].fiscal_week_key

                                       rows between 1 following and 4 following) as four_wk_sales_fcst_qty,

              avg(sales_fcst_rtl) over(partition by prime_item_key order by prime_item_key, [Fact_Customer_Sales_Forecast].fiscal_week_key

                                       rows between 1 following and 4 following) as four_wk_sales_fcst_rtl,

             

            I also will strip out unused fields next time I post a workbook to make it easier for you next time now that I know how to do that. Thank you so much for your help!

            • 3. Re: Sum of Next 4 Weeks - LOD Expression Help
              Jim Dehner

              Thanks for getting back to me - glad you found something that will work

               

              Thanks for the badge - always appreciated

              Jim