2 Replies Latest reply: Oct 28, 2015 3:36 AM by pradeep t RSS

    Set analysis -variable usage-for date selection.

    barathiraja balashanmugam

      Hi Experts,

       

      I need to show a pivot table chart which should appear as below.

       

      ChannelProcess NameSales
      EmailProcess ABC15 M
      FacebookProcess XYZ25 M
      Postal MailProcess QWE5 M

       

       

      I have source file in the form of two tables.

      Table 1 appears as follows:

       

      Order KeyOrder DateSales
      11/2/201512 M
      22/4/20152 M
      32/6/20150.5 M

      Table 2 appears as follows:

       

      Process KeyProcess NameProcess Start DateProcess End Date
      1 PProcess ABC1/1/201528/2/2015
      2 PProcess XYZ1/4/201524/4/2015
      3 PProcess QWE1/6/201527/6/2015

       

      In my requirement, there is a necessity to keep both the calendars independent.

      Now, I need to select the order dates of Table1 which falls under the corresponding process date range in the Table 2. Finally I need to show the respective total sales.

       

      In expression we have something like below.

       

      =Sum( {<$(Variable1),$(Variable2),OrderKey=p({<$(=vProcesssDateCheck)>})>} Sales)

       

      where vProcesssDateCheck='[Order Date]={">=$(=([Process Start Date]))<=$(=([Process End Date]))"}'

       

      It works fine when a particular Process Name is selected. But as a whole the Process date Range is not filtered by the variable for each record, showing '0' for Sales.

       

      Any help would be much appreciated.

       

      Thanks,

      Barathiraja