Before/After function - removing null columns

[#1]

I'm still relatively new to Qlikview and I have a pivot table where I have two dimensions on the left and one dimension at the top as columns.  The columns are years that I'm comparing and the dimensions to the left describe a type of sale.  I'm trying to compare sales from one year to the next and I've used the After() function in order to see a comparison (SUM([Units])-After(SUM([Units]))) and this works ok, but I have a column under last year's data that just basically has a null value of "-" and I was curious to know if this can be removed or if there is a better way to do the comparison.  I realize it's looking for the next value to evaluate and it's obviously not there.

Do I need to use some other function?

[jpapador]

You should try using set analysis here.  It would look something like this:

Sum({$<Year={$(=Max(Year))}>}Units) - Sum({$<Year={$(=Max(Year)-1)}>}Units)

This will give you the sum of units from the max year of the data and then subtract the sum of units from the previous year.

[#3]

Thanks for the reply jpapador. 

I think what you wrote would probably work if my years were numeric, but they aren't.  It's actually a fiscal year and so they look like this: "Fiscal Year 1213".  I realize that I could manipulate the data column before Qlikview loads it and make it numeric, but it won't work with what I'm trying to do.  Is there another way to do it? 

I thought since you used MAX here that TOP would work as if the data was sorted A-Z and so it would take the top value, but that didn't work either.  Any other ideas?

[#4]

I turned my years into a numeric data type and tried what was suggested, but I still get a column under last year that has null values. 

Is there a way to only have one column that will compare the two years?  As you can see in the table below the difference columns are blank under the 1213.  Any ideas?

		Units Sold		Difference		Difference %		Revenue		Revenue Difference		Revenue Difference %
	Year	1314	1213	1314	1213	1314	1213	1314	1213	1314	1213	1314	1213
Area	Type
Lower	Discount Price	192242	210277	-18035		-8.6%		$22840931	$22866623	($25692)		-0.1%
	Sponsor Full Price	20664	20874	-210		-1.0%		$4526072	$4352182	$173890		4.0%
	Sponsor Discount Price	2542	2622	-80		-3.1%		$1086626	$902984	$183642		20.3%
	Club	8528	7910	618		7.8%		$3895656	$3487496	$408160		11.7%
	Mini Plan	1146	10416	-9270		-89.0%		$104212	$1018008	($913796)		-89.8%
	Single -	0	13304	-13304		-100.0%		$0	$1514198	($1514198)		-100.0%
	Single - Discount	0	2919	-2919		-100.0%		$0	$231404	($231404)		-100.0%
	Single - Sponsor Discount	0	3154	-3154		-100.0%		$0	$391424	($391424)		-100.0%
	Single - Group	0	15007	-15007		-100.0%		$0	$1325405	($1325405)		-100.0%
	Area Total	225122	286483	-61361		-21.4%		$32453498	$36089725	($3636228)		-10.1%
Upper	Discount Price	144134	182234	-38100		-20.9%		$2165665	$2372219	($206554)		-8.7%
	Sponsor Full Price	4674	4674	0		0.0%		$87986	$89544	($1558)		-1.7%
	Mini Plan	424	5126	-4702		-91.7%		$12619	$172797	($160178)		-92.7%
	Single - Full	0	26876	-26876		-100.0%		$0	$851477	($851477)		-100.0%
	Single - Discount	0	2632	-2632		-100.0%		$0	$31158	($31158)		-100.0%
	Single - Sponsor	0	3294	-3294		-100.0%		$0	$65004	($65004)		-100.0%
	Single - Group	0	108226	-108226		-100.0%		$0	$1304268	($1304268)		-100.0%
	Area Total	149232	333062	-183830		-55.2%		$2266270	$4886467	($2620197)		-53.6%
Luxury	Discount Price	0	29308	-29308		-100.0%		$0	$2521060	($2521060)		-100.0%
	Full Price	0	4771	-4771		-100.0%		$0	$394509	($394509)		-100.0%
	Area Total	0	34079	-34079		-100.0%		$0	$2915569	($2915569)		-100.0%
Comp	Comp	25994	24726	1268		5.1%		$0	$0	$0
	Single	164	63699	-63535		-99.7%		$0	$0	$0
	Area Total	26158	88425	-62267		-70.4%		$0	$0	$0
Total		400512	742049	-341537		-46.0%		$34719768	$43891761	($9171993)		-20.9%

[#5 · Accepted solution]

So I figured out how to create comparisons.  I've created two expressions to give me the two years that I want to look at based on my selections:

= Sum( { <[Year]={'$(=MaxString(Year))'} >} Revenue)

= Sum( { <[Year]={'$(=MinString(Year))'} >} Revenue)

Then I created another expression to give me the difference:

= Sum( { <[Year]={'$(=MaxString(Year))'} >} Revenue) - Sum( { <[Year]={'$(=MinString(Year))'} >} Revenue)