If Statement Query

[#1]

I'm new to Qlikview and have a problem with an if statement - I was wondering if anybody can help me...

In a straight table, I have an expression 'exceeded credit limits' (which shows instances of where Customer Balances exceed credit limits), and I have an if statement which reads:

If (SUM([Balance])>MAX([Limit]),SUM(Balance)-MAX(Limit),0)

Principle (I think:) If(Condition, Then expression, else expression) So, it currently reads: If the sum of Balance field is greater than the Maximum value in Limit Field, then display the sum of Balance minus Limit, or else display zero.

My query is how do I take the zero out and just return instances of where the balance exceeds the maximum limit and nothing else.

I guess I'm asking how do I leave out the else expression. I've tried taking out the zero of the syntax, but then it gives blanks instead of zeros, i.e. Still displays these instances in the straight table.

Regards,

David

[jagannalla]

Hello,

By default qvw chart suppress the zero values or blank values. If not please check " Suppress When Value is Null " in dimension tab for paricular dim and also check "Suppress zero values " & "Suppress missing" in presentaion tab.

If you are not clear please attach sample file.

Cheers!

Hope it helps you

[#3]

Thanks Jagan Nalla for your swift response!

I have tried your suggestions, but unfortunately it didn't work.

[jagannalla]

please attach sample file.

[#5]

hi,

u cannot remove the rows with no values from the chart if that particular row is having some value for some another expression in that chart only and in this case the option "suppress when the value is null"  will also nor work.

bcoz the same record of dimension is having vaue for some other expression.

If u want to remove the null values record thn remove the another expression which is showing vaue for those records and then tick the option "suppress when the value is null" else use Null() in else.

[#6]

You just need:

If (SUM([Balance])>MAX([Limit]),SUM(Balance)-MAX(Limit)

And then supress null values, but this usually the default mode of a straight table.

[RickWild64 · Accepted solution]

Hi David,

There are two things to consider here: calculating the value, and controlling the display.

Your understanding of the If function is correct. In the form you describe, if the condition is false, the value of the expression is 0. If you leave out the third (else expression) argument or if you use null() for the else value, and the condition is FALSE, the value of the expression is NULL. This shows up as blank in a table object.

By default in a straight table, if all the expressions are null then QlikView suppresses the whole line. You can control this behaviour on the Presentation property tab, using the checkboxes for Suppress Zero-Values and Suppress Missing. You can also specify to suppress a whole line when a dimension has a null value. Unfortunately, you can’t do this for an individual expression, so if some of your columns (I’m guessing sum(Balance) and max(Limit) have real values, but you only want to see these when the balance is over the limit, you need to do something else.

You could make your other expressions conditional on your original condition, eg

If(SUM([Balance])>MAX([Limit]),SUM(Balance))

and

If(SUM([Balance])>MAX([Limit]),MAX(Limit))

Then, when the condition is FALSE, all the expressions have null values and the line gets suppressed.

A better way of doing it uses the conditional measure as a dimension:

1. Copy your original expression If(SUM([Balance])>MAX([Limit]),SUM(Balance)-MAX(Limit ))

2. On the Dimensions tab, click Add Calculated Dimension

3. In the expression editor, paste your expression, and use the aggr function to calculate the value for the real dimension, let’s say Customer. It should look something like: If(aggr(SUM([Balance]),Customer)>aggr(MAX( [Limit]),Customer), aggr(SUM([Balance]),Customer)-aggr(MAX([Limit]),Customer))

4. Click OK. Back in the Dimension tab, label your new dimension 'exceeded credit limits'. Just click the Label input field: it still works even though it looks greyed out

5. Select the new dimension and check the box Suppress When Value Is Null

This should suppress the whole row when the value of the calculated dimension is null.