topic Re: Removing the rows based on if condition in QlikView

Removing the rows based on if condition

Fri, 20 Nov 2020 18:02:30 GMT

Hi,

I am trying to eliminate or remove rows from the straight table based on if condition. I have tried applying the calculated dimension but it did not work as expected. So i applied the if condition to all the expressions. It is working fine.

Now I am facing issue to get the total of each column,Expression-> sum of rows does it but there are some expressions which are calculating percentage. What should i implement in order to calculate percentage.

So could please help me with this issue. Thanks in advance.

Regards,

Akshay

Re: Removing the rows based on if condition

jpenuliar — Tue, 10 Mar 2015 11:29:36 GMT

Hi Akshay Netalkar,

If you can attach your sample work, community might be able to help you much faster.

Re: Removing the rows based on if condition

senpradip007 — Tue, 10 Mar 2015 11:30:56 GMT

Please upload sample qvw?

Re: Removing the rows based on if condition

Wed, 11 Mar 2015 07:16:25 GMT

I have attached a screenshot of the straight table

Here in this straight table should contain only those distributors where the Achieved forecast is positive.

Similarly there is another chart where only negative values should be visible.

So I have applied a if condition present in a variable and applied it on every expression to decide if the value is positive. Similarly for the other chart having negative values.

In the present if condition i.e if($(abc)>=0,Expression,0) similarly for negative values if($(abc)<0,Expression,0)

But when I trying to calculate the percentage as marked in the screen shot the value is 0. Rest of the columns are managed using sum of rows.

Could you please help me with the filter so that I can calculate the percentage or any other way to show percentage using current if condition.

Re: Removing the rows based on if condition

awhitfield — Wed, 11 Mar 2015 11:00:38 GMT

Try Expression Total

Re: Removing the rows based on if condition

thierrytt1102 — Wed, 11 Mar 2015 11:05:23 GMT

Did you try to use the dimensionality() function?

grtz

Re: Removing the rows based on if condition

Gabriel — Wed, 11 Mar 2015 11:10:45 GMT

Hi,

Can I ask how many dimensions are in your straight table?

Well I think you can do something like this

Sum( TOTAL <Dim1,Dim2> SalesFigure)

This will give you the total. Please note that the Dim1,Dim2 is just my assumption. So if you only have 1 dimension then you only need 1 dimension in the expression.

Hopefully this answers your question

Re: Removing the rows based on if condition

jonathandienst — Wed, 11 Mar 2015 11:25:32 GMT

You need an Aggr() expression like this:

Sum(Aggr(<current column expression>, <dim1, dim2, ...>))

Where

<current column expression> is the current expression. The Aggr will return the results of this expression for the individual rows
<dim1, dim2, ...> is a comma sperated list of the dimension fields from the table. They must all be included in this list

HTH

Jonathan