Set analysis in Dimension or Supress null expression

[qlikofba]

I have an issue.

I have below is a list of the dimensions(name) and expressions (balance, previous year balance, and difference in balances)

Name    |        Balance      |   Balance of Last Year     |      Difference of (Balance - Balance of Last Year)

nm        |     sum(balance)  |       sum({$<dt={"$(=vPrevYear)"}>}balance)    | sum(balance) - sum({$<dt={"$(=vPrevYear)"}>}balance)

I tried creating a variable in the script but it needs to be created off current selections.

I then changed the expression to say

if((sum(balance) - sum({$<dt={"$(=vPrevYear)"}>}balance)) > 200000, sum(balance) - sum({$<dt={"$(=vPrevYear)"}>}balance))

The columns that are less than 200000 are displayed as -(I dont want them there). 

I only want to display the differences in the last column that are greater than 200,000.

Is there an easy way to do this?

[sunny_talwar]

Try this

Dimension

nm

Expressions:

Sum({<nm = {"=Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>} balance) > 200000"}>} balance)

Sum({$<dt={"$(=vPrevYear)"}, nm = {"=Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>} balance) > 200000"}>}balance)

Sum({<nm = {"=Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>} balance) > 200000"}>} balance) - Sum({$<dt={"$(=vPrevYear)"}, nm = {"=Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>} balance) > 200000"}>}balance)

[qlikofba]

I do not need balances greater than 200,000.

If John has 1.000.000(col 2) today, and 400,000(col 3) for the previous year end, The last column should be 600,000

If John Jr has 300,000 today and 300,001 for the previous year end, the last column should be 1(which this row should not be displayed)

[sunny_talwar]

You mean you don't need balance less than 200,000, right?

The above expression should work the way you described, have you tried them?

[qlikofba]

I do not want differences in balances(col 3) less than 200,000

[sunny_talwar]

That's exactly what I have given you my friend.

[sunny_talwar]

Can you share a sample where this isn't working?

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[qlikofba]

Sum({<nm = {"=Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>} balance) > 200000"}>} balance)

I get an error after the ) in =vPrevYear)

[sunny_talwar]

Yes, you are right, my bad. Try these

Sum({<nm = {"=Sum(balance) - Sum({$<dt={'$(=vPrevYear)'}>} balance) > 200000"}>} balance)

Sum({$<dt={"$(=vPrevYear)"}, nm = {"=Sum(balance) - Sum({$<dt={'$(=vPrevYear)'}>} balance) > 200000"}>}balance)

Sum({<nm = {"=Sum(balance) - Sum({$<dt={'$(=vPrevYear)'}>} balance) > 200000"}>} balance) - Sum({$<dt={"$(=vPrevYear)"}, nm = {"=Sum(balance) - Sum({$<dt={'$(=vPrevYear)'}>} balance) > 200000"}>}balance)

Or go traditional if statement:

If(Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>}balance) > 200000, Sum(balance))

If(Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>}balance) > 200000, Sum({$<dt={"$(=vPrevYear)"}>}balance))

If(Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>}balance) > 200000, Sum(balance) - Sum({$<dt={"$(=vPrevYear)"}>}balance))

[qlikofba]

That works now but does not solve the problem, there are still names that have a difference in balance less than 200,000 being displayed.