Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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)
Hi,
I tried it, but the result is the same as was before.
Can you post a Qlikview document that demonstrates the problem?
Hi Gysbert,
here is the model.
Thanks a lot.
Inna.
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)
Hi Gysbert,
when I choose June 9th it shows me June 9th instead of showing June 8th (Mon).
Please see attached.
Thanks,
Inna.
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.