6 Replies Latest reply: Jun 14, 2012 9:08 AM by John Kusters RSS

    Skipping lines with zero values

    John Kusters

      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.

        • Re: Skipping lines with zero values
          Christophe Brault

          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

            • Re: Skipping lines with zero values
              John Kusters

              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?

                • Re: Skipping lines with zero values
                  Christophe Brault

                  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.

                    • Re: Skipping lines with zero values
                      John Kusters

                      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.