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

# Dynamic dates in pivot table

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.

Thanks,Inna

• ###### Re: Dynamic dates in pivot table

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.

• ###### Re: Dynamic dates in pivot table

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

• ###### Re: Dynamic dates in pivot table

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)

• ###### Re: Dynamic dates in pivot table

Hi,

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

• ###### Re: Dynamic dates in pivot table

Can you post a Qlikview document that demonstrates the problem?

• ###### Re: Dynamic dates in pivot table

Hi Gysbert,

here is the model.

Thanks a lot.

Inna.

• ###### Re: Dynamic dates in pivot table

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)

• ###### Re: Dynamic dates in pivot table

Hi Gysbert,

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

Thanks,

Inna.

• ###### Re: Dynamic dates in pivot table

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.