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.