Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
The #1 reason QlikView customers adopt Qlik Sense is a desire for a modern BI experience. Read More
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Before/After function - removing null columns

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?

1 Solution

Accepted Solutions
Highlighted
Not applicable

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)

View solution in original post

4 Replies
Highlighted
Partner
Partner

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.

Highlighted
Not applicable

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?

Highlighted
Not applicable

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 %
Year1314 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%
UpperDiscount 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 - Full0 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%
LuxuryDiscount 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

Single164 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%
Highlighted
Not applicable

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)

View solution in original post