How to use Set Analysis to select a prior year

[#1]

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?

[johnw]

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.

[#3]

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!

[nwilliams]

How can you modify this to use the fiscal year in the format "FY16" .  The value is created by this line in load script:  'FY' & Right(Year(c_Date) + If(Month(c_Date) >= $(vUKFYFirstMonth), 1, 0),2) AS Calendar.UKFYYear,