topic Re: Set analysis in Dimension or Supress null expression in QlikView

Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 13:39:34 GMT

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?

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 13:41:56 GMT

Try this

Dimension

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)

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 13:47:25 GMT

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)

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 13:49:39 GMT

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?

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 13:51:23 GMT

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

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 13:55:49 GMT

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

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 13:56:32 GMT

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

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 14:28:55 GMT

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

I get an error after the ) in =vPrevYear)

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 14:32:47 GMT

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))

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 15:46:19 GMT

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.

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 15:59:08 GMT

Can you share a screenshot to show your chart?

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 16:08:53 GMT

I cannot.

All it is a chart like this

Name | Previous Year end Balance (12/31/16) | Balance of Today | Variance (todays balance - Prev year end Balance)

I only want names to be displayed if their Variance is greater than 200,000 ( or less than 200,000)

Dimension's are Name (nm)

Expressions are(Prev Year End Bal ( sum({$<dt={"$(=vPrevYear)"}>}balance) )

Balance today (sum(balance))

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

The variance displays the right data. I need to find out how to hide those variances greater than 200,000 or less than 200,000

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 16:14:49 GMT

So you are saying that you are still seeing some rows where your last expression is less than 200,000? Do you see a row like this in your chart?

Name Exp1 Exp2 Exp3

XYZ 20,000 10,000 10,000

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 16:17:58 GMT

Yes There are multiple values where exp3 is <200000

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 16:21:08 GMT

I don't know why that is the case, I can create a sample, but then it would be my data and I know it would work. Unless I see (in some way) what you are talking about, I might not be able to help you my friend. But I hope that someone might be able to sort it out, cause I understand how frustrating it can be when small things like this screw up your dashboard.

Best,

Sunny

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 16:24:30 GMT

Thanks for the help. I will use the examples you posted as a guide for the future.

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 16:29:18 GMT

I just changed one of them and now balances > 200000 are displaying in exp3.

The ones less have the symbol "-" How can i hide the rows with "-"

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 16:30:31 GMT

Go to the Presentation tab and check 'Suppress Zero Values' to hide rows with '-'

Re: Set analysis in Dimension or Supress null expression

sunny_talwar — Thu, 05 Jan 2017 16:31:33 GMT

For future reference

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

If you have got what you wanted, please close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

Re: Set analysis in Dimension or Supress null expression

qlikofba — Thu, 05 Jan 2017 16:34:35 GMT

That is checked for the exp3 but it still shows.