Good afternoon. I have a group based on a SQL date field, using =Year(X), =Month(X), and =Day(X). Trying to filter down to the month level is giving me unexpected results.
The data has daily records for the period 2009/01/01 to 2011/01/26. On load, I see 2009, 2010, 2011 - as expected. If I click on 2011, it skips the month layer, because there's only one, and shows 1, 2, 3, through 26 - as expected. If (from a clear slate) I filter for 2010, I see Jan through Dec - as expected.
Here's where I'm thrown: if I filter for 2010 and then choose Jan, QlikView skips the Day layer and sends me back to the Year layer. It shows 2009, 2010, and 2011, with 2011 highlighted, not 2010 as I would expect. Meanwhile, my pivot chart shows 2009, 2010, and 2011. Based on this and on the totals it gives, I think what's happened is that QlikView is not drilling from 2010 to 2010/01, but rather replacing the "year = 2010" filter with a "month = Jan" filter. My data has three Januaries, one each for 2009, 2010, and 2011, and that's what's shown.
In support of this hypothesis, if I click (from a clear slate) on 2009 and then Feb (or Mar, or any month but Jan), my pivot table shows just 2009 and 2010: because my data only has two Februaries.
If I select "2011" and then "7", my pivot table shows me 2009, 2010, and 2011, with numbers that are about right for 12 days. So it seems to be filtering for "day = 7", for all years and all months.
Is it not correct to use "=Month(FieldName)" and "=Day(FieldName)" for the month and day layer of a date group? I tried "=MonthName(X)" and ="DayName(X)", which despite the names give "MMM yyyy" and "yyyy/MM/dd". These disambiguate months and days, which lets the group work as expected, but it's ugly and wastes a lot of space in charts. Isn't the whole concept of a hierarchy that if I step from X in layer 1 to Y in layer 2, that I want only elements that are, well, X in layer 1 and Y in layer 2?
