topic Re: Dynamic dates in pivot table in QlikView

Dynamic dates in pivot table

Mon, 08 Jun 2015 12:14:07 GMT

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

Re: Dynamic dates in pivot table

Gysbert_Wassenaar — Mon, 08 Jun 2015 12:37:12 GMT

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

Mon, 08 Jun 2015 12:40:34 GMT

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

Gysbert_Wassenaar — Mon, 08 Jun 2015 12:47:55 GMT

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

Mon, 08 Jun 2015 12:57:16 GMT

Hi,

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

Re: Dynamic dates in pivot table

Gysbert_Wassenaar — Mon, 08 Jun 2015 12:59:04 GMT

Can you post a Qlikview document that demonstrates the problem?

Re: Dynamic dates in pivot table

Mon, 08 Jun 2015 15:10:53 GMT

Hi Gysbert,

here is the model.

Thanks a lot.

Inna.

Re: Dynamic dates in pivot table

Gysbert_Wassenaar — Mon, 08 Jun 2015 15:46:40 GMT

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

Tue, 09 Jun 2015 08:52:47 GMT

Hi Gysbert,

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

Please see attached.

Thanks,

Inna.

Re: Dynamic dates in pivot table

Tue, 09 Jun 2015 09:01:53 GMT

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.

Re: Dynamic dates in pivot table

Tue, 09 Jun 2015 10:53:42 GMT

Somebody?

Can help me?

It is VERY VERY URGENT!!!