Yearly comparisons

[#1]

Sorry I am so new to this. I am trying to figure out how to get the difference between years. Here is what my data looks like:

                                        Feb     Mar     Apr

Style A     2009     POS     100     200     300

Style A     2010     POS     200     100     300

I am wanting to add in the percent change from 2009 to 2010 (2010POS/2009POS-1)

                                        Feb     Mar     Apr

Style A     2009     POS     100     200     300

Style A     2010     POS     200     100     300

                         Change 100%   -50%     0%          

Thanks in advance for your help.

[johnw]

Well, if it's always going to be 2010 vs. 2009, you can hardcode the values rather than using the year as a dimension:

2010 = sum({<Year={'2010'}>} Something)
2009 = sum({<Year={'2009'}>} Something)

Change = "2010"/"2009"-1

If you want the user to select the more recent year, and compare to the previous year:

Selected Year = sum({<Year={'$(=max(Year))'}>} Something)
Previous Year = sum({<Year={'$(=max(Year)-1)'}>} Something)
Change = "Selected Year"/"Previous Year"-1

If you want to list multiple years, and see a difference for each, you could generate an "AsOf" table to support this more directly.  See attached.  It's not your specific example, but it's an example of the technique.

[#3]

I must be dumber than I realize because Istill can't get this to work. Your equationlooks like it would work but for some reason I can't get ittoo. Let me see if I can weed it out. The first possible issue is that I amusing Qlikview v. 7.2. Are the syntaxes the same? Next let me list the fields Ihave and maybe you can show me how they fit into your formula.

Accounting Year

Accounting Month

POS Units

Style

Looking at your example sheet I wasn’t sure how you wereable to create “Type” with Current and Previous.

Are “Selected Year” and “Previous Year” entered as expressions?

Thanks again for your patients and help.

[johnw]

Ah!  Yeah, nothing I posted is supported until version 8.5, when set analysis was introduced.  I'll need to think of a different approach and get back to you later, but I thought I should at least let you know it's not your fault it isn't working!

[johnw]

OK, attached are a couple solutions that should work in version 7.2.  Both involve data changes.  The most common solution is probably to build an "island table" with all the year values under a different field name.  Since there's then no link to the main table, you have access to EVERY row in the main table for each island year.  You can then use a sum(if()) to narrow down to the data you want.  Since every row in your chart must apply the if() to every row in the main table, this can be prohibitively slow for large charts on large tables.  It's a fairly simple solution for small charts or small tables, though.

Another solution is to build an AsOf table.  It appears that you need a more complicated version of this table than works in version 8.5 and above, though.  Instead of linking each AsOfYear by Type to the current or previous year, you appear to have to link them to the specific IDs from the main table that correspond to the current or previous year.  That done, you build your table with AsOfYear instead of Year as the dimension.  Each row in this table then links only to IDs in the current or previous year.  You then use the sum(if()) to keep only the year you want for that column.  While both approaches use a sum(if()), the AsOfYear already significantly narrows your data set before passing what's left to the sum(if()), so should be much more efficient as the number of years in your data model climbs.