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

# Sum of Next 4 Weeks - LOD Expression Help

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

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

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

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

Thanks for the badge - always appreciated

Jim