HeyZeusChris wrote:1) I think there might be a problem with the formula
sum({<Date*={">=$(StartDate) <=$(EndDate)"}>} Sales)
I don't understand the formula so I am not sure exactly what is wrong. Naively, I would guess there the '*=' part isn't right because when I edit the expression I get a red squiggly underline. Can you take a second look? Or better yet explain the logic of the formula?
The *= part is intersection. Normally people just use =, but that overrides any selections you've made, which I don't think is clearest for the users. As a user, let's say I use the two variables to select all of 2010. If I then select only May 21 2010 from a Date drop down, using Date= will ignore that selection, while using Date*= will respect that selection, and only show you May 21 2010. However, if you select May 21 2011, you'll get nothing, because the intersection of your selection in the Date field and your "selections" in the start and end dates is null. The rest of the formula is just saying that you want dates between the start date and the end date, inclusive. The $() part is called dollar sign expansion. What it should do is insert the values of your date fields into the expression. One possible problem would be if StartDate and EndDate have a different format than Date. QlikView is doing a text comparison, not a date comparison, so the formats have to match. If necessary, you could enforce this by replacing the $(...) parts with $(=date(...),'YY/MM/DD') or whatever format you need. Another possible problem is when OTHER date fields are in the application, and you've made selections that are in conflict with the range from the variables. The above is consistent, in that it will respect all selections, and if there's no overlap, it will return nothing. If you want it to ignore all date selections except for the variable, you'd need to do something like this:
sum({<Year=,Quarter=,Month=,Date={">=$(StartDate) <=$(EndDate)"}>} Sales)
When you use an = with nothing after it, it tells the system to just ignore selections in that field. So you list every date-related field like that, and then put the set on the Date field itself.
And of course I could have just made a simple mistake that's keeping it from working. Not seeing it, though.
HeyZeusChris wrote:2) Also, Using the calendar table you recommended is working well. The only problem is right now my dates are showing as '8/1/2008 12:00:00 AM', which is cumbersome. Where can I actually format this so that it just shows '8/1/2008'?
Look at the top of your script. It sounds like you've somehow set your default date format like this:
SET DateFormat='M/D/YYYY hh:mm:ss TT';
When what you want is this:
SET DateFormat='M/D/YYYY';
There are plenty of other ways to change date formats though, so that might not be it. We'll start there, though.
Oh, hah. I just realized that that's probably the problem with the set analysis. Fix the date format, and the set analysis will probably work.
HeyZeusChris wrote:3) Is it possible to switch between the drill down levels without actually drilling into just one year, or quarter? To elaborate, is it possible for a user to see a graph of these sales by quarter for 3 or 4 years? Right now I can only see the quarterly drill down if I select just one year, say 2008. But a user might want to see the quarterly trend over the last 6 years, or a monthly trend for the past 3 years.
Use a cyclic group instead of a drill down group.
