topic Re: Before/After function - removing null columns in QlikView

Before/After function - removing null columns

Wed, 17 Jul 2013 19:20:10 GMT

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?

Re: Before/After function - removing null columns

jpapador — Wed, 17 Jul 2013 20:46:34 GMT

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.

Re: Before/After function - removing null columns

Wed, 17 Jul 2013 20:59:19 GMT

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?

Re: Before/After function - removing null columns

Thu, 18 Jul 2013 14:44:50 GMT

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%

Re: Before/After function - removing null columns

Wed, 02 Oct 2013 18:04:14 GMT

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)