Show 20 weeks forward and total of prevoius weeks in a pivot

[Anonymous]

Hi!

I have an application where I'm trying to show the sum of each customers invoices per week 20 weeks forward and the total of all overdue from prevoius weeks.

This based on current week.

Customer, WeekPrv, Week1, Week2, Week3 ... etc is the layout I'm trying to get.

I have tried this set expression for the coming weeks:

SUM({$ < DateYearWeek     = {">=$(=varYearWeekToday)<=$(=varYearWeekPlus20)"}>} InvAmount)

DateYearWeek is the week from my datasource.

varYearWeekToday is a variable for the current week.

varYearWeekPlus20 is current week + 20

However, the pivot is showing all weeks but with zero values for all other weeks found in my datasource.

Any suggestions?

[Anonymous]

Should have pointed out that this is a v 11 application.

[Miguel_Angel_Baeyens]

Hi Mathias,

My guess is that this has something to do with the format of the field DateYearWeek. Is this a numeric field so it can work in a range as you did in the set analysis part of the expression? Note that if the field is a literal (a string) the range ">=Something<=Something Else" will not work.

Apart from that, you may need to remove the "=" sign preceding the variable name if the variable definition is another expression and has been defined with the equal.

Hope that helps.

Miguel

[Anonymous · Accepted solution]

Thanks for your reply!

However I found the problem to be caused by the variable varYearWeekPlus20 that was calculated wrongly.

//Mathias