10 Replies Latest reply: Jun 9, 2015 6:53 AM by Inna Shnaiderman RSS

    Dynamic dates in pivot table

    Inna Shnaiderman

      Hi,

      I need to build pivot table which will present data according to dynamic date selection in calendar.

      Also it will present the relevant week according to this selection.

      A relevant week is Monday-Sunday.

      It means that if I select 07/06/2015(Sun) in Calendar, Pivot will show data per date for dates:25/05/2015-06/06/2015.

      If I select 03/06/2015 (Wed) in Calendar, pivot will show date per date for 01/06-02/06.

      I have a variable which shows how long back I should calculate according to max(date) selection:

      VDWeekDayReport which is equal to:

      if(WeekDay(max(Date))='Tue',vDLast1,
      if(WeekDay(max(Date))='Wed',vDLast2,
      if(WeekDay(max(Date))='Thu',vDLast3,
      if(WeekDay(max(Date))='Fri',vDLast4,
      if(WeekDay(max(Date))='Sat',vDLast5,
      if(WeekDay(max(Date))='Sun',vDLast6,
      )))))))

       

      while vDLast1==date(max(Date)-1) and so on.

       

      I built the following expression :

       

      count({<Date={'>=$(vDWeekDayReport))<=$(=Date(Max(Date)))'}>}DISTINCT TCKTID)

      but when I choose 0306/2015, for example it shows me data since the beginning of DB(05/02/2015) and not for dates 01/06-02/06 as I expect.

       

      please your help here.

       

      Thanks,Inna