topic Drill Down by date problem? in QlikView

Drill Down by date problem?

Mon, 01 Jun 2015 13:43:42 GMT

Hello World!

I'm currently developing a report that requires some Drill Down options and I'm struggling with the following problem.

Currently we get the data based on days, so my granularity is based on day. To easy navigate, I created a Drill Down group as follows

The problem happens when I select a week between Months like Week 2015/18, then the drill down jumps back to Months instead of showing me the days.

It also happens to don't show properly the weekly values from the month perspective as it is not counting the full Week 2015/18

As a workaround I simply remove the Month view and then I get the data correctly. The problem is that then I miss the Monthly dimension, that in some cases it is required.

My question:

- Am I doing correctly the Drill Down?

- If so, is it possible to create a bug with this case and have it fixed for future versions?

- Is there any alternative to add the monthly view with the correct weekly overview?

Thanks in advance, I'm just 2 months working with QlikView no previous training, so forgive me any rookie mistake!

Julio

Re: Drill Down by date problem?

Mon, 11 Jan 2016 14:21:37 GMT

I've being looking into this problem since then, I conclude that Weeks and Monts/Quarters exclude each other. Thus if you want to add Quarter/Months and Weeks in the same QlikView App my best workaround is to add a rule at document level that clear the excluding filters ( selecting Month or Quarter clear Week and viceversa).

By doing that everytime a week is selected, automatically the Quarter and Month are cleared avoiding any possible missleading and viceversa. The problem still remains at drill down option.

Re: Drill Down by date problem?

ogster1974 — Mon, 11 Jan 2016 14:38:20 GMT

Try posting this in the QlikView forum not Qlik Sense. You will get the proper help there.

Re: Drill Down by date problem?

Mon, 11 Jan 2016 15:11:12 GMT

can you base each level on the lower level, rather than the base level?

e.g. Week is WeekStart([Date])

Month is MonthName(WeekStart([Date]))

etc.

If you materialise these fields into your load script, it should give a valid hierarchy

Re: Drill Down by date problem?

Mon, 11 Jan 2016 15:28:57 GMT

Thanks, I didn't realize I was in the wrong place .. I'll see how to move that to the right place.

Re: Drill Down by date problem?

MarcoWedel — Tue, 12 Jan 2016 00:58:23 GMT

Hi,

selecting in calculated dimensions actually selects in the fields used in these calculations.

As all of your drill down dimensions are derived from your date field, each selection changes the previous selection in the same field.

Selecting a week that spans over two months selects dates in those two months.

The upmost dimension in your group having more than one value will then be "MonthName" again.

So I guess it works as designed.

My suggestion would be to precalculate your calendar fields in the script using some code like this:

table1:
LOAD Date,
     Values
FROM
bogus_adata.xlsx
(ooxml, embedded labels);

tabCalendar: 
LOAD *, 
     Day(Date) as Day, 
     WeekDay(Date) as WeekDay, 
     Week(Date) as Week, 
     WeekName(Date) as WeekName, 
     Month(Date) as Month, 
     MonthName(Date) as MonthName, 
     Dual('Q'&Ceil(Month(Date)/3),Ceil(Month(Date)/3)) as Quarter, 
     QuarterName(Date) as QuarterName, 
     Year(Date) as Year, 
     WeekYear(Date) as WeekYear;   
LOAD Date(MinDate+IterNo()-1) as Date 
While MinDate+IterNo()-1 <= MaxDate; 
LOAD Min(Date) as MinDate, 
     Max(Date) as MaxDate 
Resident table1;

resulting behaviour:

hope this helps

regards

Marco

Re: Drill Down by date problem?

Tue, 12 Jan 2016 08:14:03 GMT

UPDATE: WeekStart(Date) also in year.

Hi Andrew,

Now is working for me, here you have the script I use and now I can easily drill down including week

LOAD Date
  ,Year(WeekStart(Date)) as Year
  ,QuarterName(WeekStart(Date)) as Quarter
  ,Month(WeekStart(Date)) as Month
  ,Week(Date) as Week
  ,Day(Date) as Day
    ,Values
FROM
bogus_adata.xlsx
(ooxml, embedded labels);

Thanks!

Re: Drill Down by date problem?

Tue, 12 Jan 2016 08:22:35 GMT

Hi Marco,

Thanks for your feedback, I'll have a look at it too!

I solved the case with Andrews feedback, in my case it was very simple, when I drill down from month to week based on Date it was pre-selecting the month and excluding the days from the previous month, by using the week function within the month function it doesn't exclude the days from the previous month giving me the desired behaviour.

Regards,

Julio