Running Selection on invoice date based upon dimension

[#1]

Hi Guys,

I am stuck in an expression where I need to calculate the sum of on-demand quantity in a table with InvoiceAltYearMonth(Year-Month) as a dimension. For every year month, I need to look at a period which falls between the last day of that year month and a date 'OnDemandCode' weeks from it.

Chart Dimension: InvoiceAltYearMonth(Year-Month)

Start Date (based upon 'Go back X weeks' selection) (works fine):

=Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat)

Last Day of Every month (works fine): 

=Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)

In the attached file, you can see that the start dates and end dates are found accurately based upon the InvoiceAltYearMonth dimension. However, I am unable to calculate the sum of on demand quantity between Start Date (based upon 'Go back X weeks' selection) and Last Day of Every Month

PS: The start date is dynamic and is based upon the selection. Therefore, it's not necessary that the start date will always fall within the month in the dimension.

I have tried two approaches:

1. Create variables and write expression

vStartDate:          =Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat)

vEndDate:           =Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)

Final Expression Approach 1:      (Sum({<InvoiceDate={">=$(#vStartDate)<=$(#vEndDate)"}>} OnDemandQuantity))

Through this approach, the expression is calculated accurately only between 2/18/2012 and 2/25/2012. This happens because the variables in my set analysis are calculated only once, and not for every dimension which I actually expect to happen !

2. Write an expression by explicitly defining the start date and end date in the set analysis:

Final Expression Approach 2: (Sum({<InvoiceDate={">=$(#=Date(Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate)-7*OnDemandCode, DateFormat))<=$(#=Max({<InvoiceAltLastDayOfMonth={1}>} InvoiceDate))"}>} OnDemandQuantity))

This approach gives all zeros.

Can anyone please help me in finding a solution to this problem ? The qvw and xlsx files are attached.

[azimabadi]

Hi all,

i have the same problem. Nobody can offer an approch?