1 Reply Latest reply: Jul 2, 2012 12:47 PM by Peyman Azimabadi RSS

    Running Selection on invoice date based upon dimension

      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.