Dynamic dates in pivot table

[#1]

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

[Gysbert_Wassenaar]

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.

25/05/2015-06/06/2015 is thirteen days. 01/06-02/06 is two days. I don't understand the logic.

[#3]

Hi Gysbert,

it is a mistake .

When I choose 07/06/2015(Sun) , Pivot will show data per date for dates:01/06/2015-06/06/2015.

Thanks,

inna

[Gysbert_Wassenaar]

So you want the date range between the weekstart of the selected date and the day before the selected date? If so try:

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

[#5]

Hi,

I tried it, but the result is the same as was before.

[Gysbert_Wassenaar]

Can you post a Qlikview document that demonstrates the problem?

[#7]

Hi Gysbert,

here is the model.

Thanks a lot.

Inna.

[Gysbert_Wassenaar]

If you select a value in the Day value and you want to see the other dates in the table then you need to override that selection in the expression. Try this one: count({<Day=, Date={'>=$(vDWeekDayReport))<=$(=Date(Max(Date)))'}>}DISTINCT TCKTID)

[#9]

Hi Gysbert,

when I choose June 9th it shows me June 9th instead of showing June 8th (Mon).

Please see attached.

Thanks,

Inna.

[#10]

on the other hand if I use

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

and choose June 9th I am getting all dates from 01/06/2015 and till 09/06/2015.

Please advise.

it is very urgent as today I have to submit it to the customer.