joncurry wrote:Thanks very much for your response. I don't think we can use what you suggest. Each piece of data is a sales event at the order line level, and it happened on a particular date. I can't see any logical way to associate it with a prior year, because it didn't happen in a prior year.
You appear to have it solved now, but if so, it shows that I didn't fully understand your question. Your final expression, since it includes "only", will ONLY work when a single Fiscal Year is selected. It will NOT work if you want to see multiple Fiscal Years at the same time. Either of the approaches I mentioned will still work in that case.
Assuming you DO want to be able to see more than one fiscal year at once, you mentioned that you didn't think you could use what I suggested. It sounds like you thought it was incorrect to associate, for instance, fiscal year 2007-2008 data with fiscal year 2008-2009. In a sense you're right, but there's also nothing strictly wrong with it. Note that the way the tables I suggested connect together, what the tables REALLY say is "If I select fiscal year 2008-2009, and then ask what the data for the PREVIOUS fiscal year is, then this piece of data from fiscal year 2007-2008 should be returned". That IS correct. You aren't actually saying that the 2007-2008 data IS 2008-2009 data, you're just saying it is associated with it when you ask for the previous year. So you just need to sort it all out in the expression, which is what my example did.
If you'd like I can try to work out how my example could be applied to your data, but that would be pointless if your solution works for you, as mine is more complicated and apparently solves a problem that you don't need solved.
joncurry wrote: I've attached a picture of the table viewer in case it helps explain what I'm talking about. There actually is a separate calendar table.
I was interested in your "easy enough" comment, because I sure didn't find it to be that way.
Ah, well, I meant something different when I mentioned having a calendar "completely separated from your data". Your calendar is on a separate table, yes. But this table is connected to your data. What I meant was, in addition to that calendar, adding a NEW calendar with NO connection to your data. That is a common solution to many date-related problems. You would have your main data connected directly to a calendar, as you do today. For the sake of argument, call the fields ConnectedDate, ConnectedYear and so on. Then you make a second calendar that has no connection to your data. For the sake of argument, call the fields DisconnectedDate, DisconnectedYear and so on.
You then have your user make selections from the Disconnected calendar, not the Connected calendar. That way, they aren't actually narrowing down the data set at all. You then use IF statements in your chart to make the necessary connections.
So for this example, say we have ConnectedFiscalYear and DisconnectedFiscalYear. Your chart has the DisconnectedFiscalYear as the dimension, and that is the field you let the users select. Oh, for simplicity, I would also define these fields with dual(), like dual('2008-2009',2008) so that you can do simple math. You then use expressions like this:
sum(if(ConnectedFiscalYear=DisconnectedFiscalYear ,Sales))
sum(if(ConnectedFiscalYear=DisconnectedFiscalYear-1,Sales))
That avoids set analysis completely, and to me is a simple solution. The problem, as I mentioned, is that it is highly inefficient from a processing point of view. QlikView must scan through ALL years looking for a match, rather than zooming in directly on the years of interest. If you have a lot of data, it can bog down a lot. So I try to avoid that approach when I can, at least in big applications. And that's why I mentioned it only to reject it as a legitimate solution just as fast. I wanted to acknowledge that it would work, but suggest something different.
