12 Replies Latest reply: Nov 20, 2015 4:08 AM by Neil Williams

# How to use Set Analysis to select a prior year

We have a field in our data called "Fiscal Year." The values are strings that look like '2007-2008', '2008-2009', etc. The reason for this is that our fiscal year runs from a certain Sunday near the end of October / first of November for either 52 or 53 weeks, ending on a Saturday also near the end of October.

I would like to use Set Analysis to come up with a "Prior Year" expression in our table charts, but I can't figure out how to do it.

If I create a chart and include a dimension for "Fiscal Year", then create a calculated dimension called "Prior Year" with this expression:

=(SubField([Fiscal Year],'-',1) - 1) & '-' & (SubField([Fiscal Year],'-',2) - 1)

… when I select fiscal year 2008-2009. the Prior Year column will display "2007-2008."

The expression "Sum ([Gross Sales])" will give me the sum of the gross sales for the fiscal year that is currently selected. I expected that if I created another expression like "sum( {\$<[Fiscal Year] = {[Prior Year]}>}[Gross Sales] )" that it would return the sum of gross sales for the current selection, except with a new selection for "Fiscal Year", being the year before the one making up the Sum([Gross Sales]) column. It didn't work.

I tried various combinations, including sum( {\$<[Fiscal Year] = {(SubField([Fiscal Year],'-',1) - 1) & '-' & (SubField([Fiscal Year],'-',2) - 1)}>}[Gross Sales] ), and I can't get anything to work.

However, if I "hard code" a new fiscal year in there, like this:

sum( {\$<[Fiscal Year] = {"2007-2008"}>}[Gross Sales] )

I get the result I was looking for, except it shows up on its own line in the chart.

Is there any way I can construct a chart that will give me the sum of sales for the year selected in one column and the same information for the prior year in the adjacent column, without using QlikView's built-in date functionality, which won't work for us?

• ###### How to use Set Analysis to select a prior year

This seems to be an issue that stumps everyone. I'm still struggling to solve the exact same issue.

• ###### How to use Set Analysis to select a prior year

Easy enough with a calendar completely separated from your data, but highly inefficient from a processing point of view.

One approach would be to connect TWO financial years to the same data,

ID Year Type Fiscal Year
12345 Current 2008-2009
23456 Previous 2008-2009
23456 Current 2007-2008
34567 Previous 2007-2008

ID Sales Cost
12345 5000 4000 // 2008-2009 data
23456 4500 3800 // 2007-2008 data
34567 4200 3900 // 2006-2007 data

So when you select Fiscal Year 2008-2009, you're actually selecting TWO year's worth of data, the current year and the previous year. Then all of your charts must distinguish between the two:

sum({<"Year Type"={Current}>}Sales) // For 2008-2009, selects ID 12345
sum({<"Year Type"={Previous}>}Sales) // For 2008-2009, selects ID 23456

If you don't want to have to specify the year type to see current data, select it, lock it, hide it, and do whatever you need to to keep the user from changing it. Then only expressions for the previous year would need set analysis.

See attached implementation of the above example.

• ###### How to use Set Analysis to select a prior year

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. We put all this detailed data into our app, and then let the power of QlikView sum it up by day, week, month, quarter, fiscal year, calendar year, customer, customer group, item, item group, and so on, and it works wonderfully well. I just had this glitch with current year vs. prior year.

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.

Anyway, after reading the response from ajay143, quite a few of your previous posts on the subject of set analysis (you seem to understand it really well - I hope I get there some day), the training manuals, and trying a bunch of different approaches to the syntax, I finally got it to work.

I'll post the solution in a separate reply.

Thanks again

• ###### How to use Set Analysis to select a prior year

`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.

• ###### How to use Set Analysis to select a prior year

Thanks, John. You're right - my solution only works if the user has a single fiscal year selected, and that's fine for the current application. I couldn't make sense of the example you sent, because i was assuming that ID represented a customer or an order, but I think I see now that it's really a unique key for each row of data, and your approach is to record, for every row, the value of the current and prior fiscal years. I'm still a little confused because I think ID 23456 has the current year as 2007-2008 and the prior year as 2008-2009, and that threw me a little.

That's an interesting approach, and could be really useful if our application grows to the point that set analysis is too slow, or somebody wants to see multiple years at once..

I'm thinking there wouldn't have to be a separate joined table - you could have the columns for Current Fiscal Year and Prior Fiscal Year right in the main data table - or am I still missing something?

Also, thanks for explaining about the appproach where you can use the disconnected calendar and IF() logic as an alternative. That could come in handy, too.

Regards,

Jon

• ###### How to use Set Analysis to select a prior year

Right, the ID would be a unique ID for a row of data. I should have clarified what it was, since it's important. An ID like that might be available from one of your data sources, or you might use use recno() as ID, which is something I often do.

I agree that my current year vs. prior year is confusing. I should have arranged the table in the opposite order, and maybe it would have made more sense:

Fiscal Year Data Type Row ID
2008-2009 Current Year 12345
2008-2009 Previous Year 23456
2007-2008 Current Year 23456
2007-2008 Previous Year 34567

Row ID Sales Cost
12345 5000 4000 // 2008-2009 data
23456 4500 3800 // 2007-2008 data
34567 4200 3900 // 2006-2007 data

So for an example of how to read it, "If I select Fiscal Year 2008-2009, and I ask for the Current year data, give me ID 12345 (the 2008-2009 data). If I instead ask for the Previous year data, give me ID 23456 (the 2007-2008 data)."

Now, you suggest that we wouldn't need a separate table. Well, our starting table probably looks like this:

ID Sales Cost Fiscal year
12345 5000 4000 2008-2009
23456 4500 3800 2007-2008
34567 4200 3900 2006-2007

So far so good. But what are you proposing we add? Something like this?

ID Sales Cost Fiscal Year Prior Fiscal Year
12345 5000 4000 2008-2009 2007-2008
23456 4500 3800 2007-2008 2006-2007
34567 4200 3900 2006-2007

The problem there is that selecting Fiscal Year 2008-2009 still only gives you one year of data. Now, you can also SEE that the Prior Fiscal year is 2007-2008, but QlikView isn't automatically doing anything with that data. I believe you could make it work for a single selected year, as then only("Prior Fiscal Year") would return a result that you could use in a set analysis expression. But if you want to see multiple fiscal years at once, set analysis won't distinguish between the rows in your table. There MIGHT be a way to pull it off, I won't guarantee that there isn't, but I'm not personally seeing how to pull it off with a structure like that.

Anyway, none of this appears to be relevant to your actual problem, and I think your actual solution to your actual problem is the correct one.

• ###### How to use Set Analysis to select a prior year

Got it now. Thanks!

• ###### How to use Set Analysis to select a prior year

Since this thread has been referenced, I thought I should post my most recent version. It occurred to me that you don't need set analysis AT ALL for the example. Instead, you just need Year Type as a dimension in a pivot table. Easy. See attached.

• ###### Re: How to use Set Analysis to select a prior year

Hi John.

I am in dire need of assistance with a similar query.

I have two variables that find the equivalent min and max period of the prior year based on the current period selected. Eg. If the user selects Nov-13 till Dec-13, then each of my variables store Nov-12 and Dec-12 respectively.

As with the other guys, my variables work in theory in terms of getting the correct periods based on what was selected. Getting it to work in QV set analysis isn't so straight forward however.

I tried the ONLY function as per this blog (even though I am aware that it will not allow multiple selections).

I could not get it to work. Nonetheless, the user will need to make more than one selection and I am assuming continuing periods only, i.e. Aug-13 till Dec-13 as an example and not also add Feb-13 to this (I would prefer a solution for this as well but it's not critical).

Please see below a snippet of my set analysis:

sum({\$<MonthSeq = {">=\$(=vMinMonthSeqPriorYear)<=\$(=vMaxMonthSeqPriorYear)"}>} [Gross Sales (Excl)] )

using "ONLY", I coded:

sum({\$<MonthSeq = {">=\$(=only(vMinMonthSeqPriorYear))<=\$(=only(vMaxMonthSeqPriorYear))"}>} [Gross Sales (Excl)] )

Either way, my results return zero. Would appreciate any assistance.

Thank you!

• ###### How to use Set Analysis to select a prior year

As per ur query if u have a column Fiscal year with value having a character set inside like 2007-08,2008-09 then set analysis is not working

i have faced the same problem what i did is i have created a new column named as Fiscalyear1 in the Edit script having values stored like

200708,200809 then in the application i have created a list box showing this Fiscalyear1 then in set analysis i have used the expression

sum({Fiscalyear1 = { \$(#=only (Fiscalyear1 ) ) } Amount ) // for 200809

sum({Fiscalyear1 = { \$ ( #=left(Fiscalyear1 )-1&right(Fiscalyear1 ) -1 ) } Amount ) // for 200708

hope this works for u

• ###### How to use Set Analysis to select a prior year

Thanks, ajay143. I couldn't get it to work exactly as you suggested, but it got me started in the right direction, and I finally got it to work. It turned out that I can use the existing [Fiscal Year] field that looks like "2008-2009." The secret seems to be that you need double quotation marks in the right position, which isn't easy to determine.

Here's the expression for Current Year:

`sum({\$<[Fiscal Year]={"\$(=Only([Fiscal Year]))"}>} [Gross Sales] )`

Here's the one for Prior Year:

`sum({\$<[Fiscal Year]={"\$(=Only((SubField([Fiscal Year],'-',1) - 1) & '-' & (SubField([Fiscal Year],'-',2) - 1) ))"}>} [Gross Sales] )`