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
