Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

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.

please your help here.

Thanks,Inna

10 Replies

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.


talk is cheap, supply exceeds demand
Not applicable

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)


talk is cheap, supply exceeds demand
Not applicable

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?


talk is cheap, supply exceeds demand
Not applicable

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)


talk is cheap, supply exceeds demand
Not applicable

Re: Dynamic dates in pivot table

Hi Gysbert,

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

Please see attached.

Thanks,

Inna.

Not applicable

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.

Please advise.

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

Community Browser