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.
