Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Skipping lines with zero values

I made a table with fixed assets showing the following columns:

1. Asset number

2. AssetDescription

3. Cost,

4. Accum. Depreciation

5.Net Book Value.

Column 3 is an expression with formula (x)

Column 4 is an expression with formula (y)

Column 5 ( net book Value) is the result of column 3 -/- Column 4 i.e. Formula(x) -/- formula (y)

The user asked me to switch between Only showing columns with Net Book Value > 0 or Showing all columns ( Inlcusing NBV = 0 )

I van make a variable and let the user choose the option, but how can i implement the selection i.e. showing only columns with NVB > 0 or showing all columns ? I can see/find no option to conditionally skip a line in the table depending on a condition.

1 Solution

Accepted Solutions
christophebrault
Valued Contributor

Re: Skipping lines with zero values

I though you were at script level sorry...

you can try to create a list Box using an expression :

aggr( if(Expression 3 / Expression 4 = 0,'Y','N'),Asset number)

6 Replies
christophebrault
Valued Contributor

Re: Skipping lines with zero values

Hi,

A solution could be to create a sixth column. This one will say if NBC >0 is true or false. With this, you can use it in a list box to simply reduce your data.

write :

if(column 3 -/- Column 4)>0,'Positive NBC','NBC = 0') as NBC

Not applicable

Re: Skipping lines with zero values

Sorry, but i'm a bit new in QV, and dp not understand the solution.

I created a 6th column (Exprwession saying:

If ( Expression 3 -/- expression 4  =0, 'Y','N')

and indeed in the table the column is showing 'Y' if the NBV = 0, and  'N'if NBV <> 0.

But then i'm lost with your solution on how to use this in a list box to simply reduce my data?

What do i have to do with the list box then?

christophebrault
Valued Contributor

Re: Skipping lines with zero values

If your script worked well, you should have Y for the lines where NBC = 0 and N where NBC <>0.

First check if it's the case.

With this field, you can simply select only lines <> 0, =0 or both. Creat a list box with this field and you can use it.

An other solution could be to create a Button and set an action to select N in your field 6 when user clic and clear this selection when reclic. If this solution meet your needs i can explain more on How To Do.

Not applicable

Re: Skipping lines with zero values

I think why we do have a mis communication.

When i said i have a table, i meant i defined a table object in QV, based on 3 Data tabels/views.

1 table/view is fixed assets

1 table/view is fixed assets / years ( Per Asset/Year i have the starting balance)

1 table/view is fxied assets/Years/Periods ( per Asset/Year/Period i have the period depreciation amounts)

Based on these 3 MSSQL tables which i loaded in the QV script, i build in QV 1 'Table' object with 2 dimensions

( Asset number and asset description from table Fixed assets)

Then i have columns 3,4,5 which are all expressions like for example columns 3 Costs =

sum({$<abyYear={$(Year)},abyBalanceCode={1} >} abyAmountBegBalancePY) +

sum({$<abyYear={$(Year)},abpPeriod={">=1 <=$(Periode)"},abyBalanceCode={1} >} abpAmount)

So Column 5 showing the NBV is also an expression which has an amount as result.

The user wants to chose to show also these lines when they are 0,00 or not.

So when i did what you said, i put the expression of column 5 in a list box, but this was not working.

christophebrault
Valued Contributor

Re: Skipping lines with zero values

I though you were at script level sorry...

you can try to create a list Box using an expression :

aggr( if(Expression 3 / Expression 4 = 0,'Y','N'),Asset number)

Not applicable

Re: Skipping lines with zero values

Thanks a lot. This was the answer i was looking for.!

Community Browser