topic many questions about straight table in QlikView

many questions about straight table

Wed, 15 Dec 2010 06:02:54 GMT

I have such a table.

How can I filter sum(Sales) between 50 and 100 without selected any fields when the table 1st displayed.

many questions about straight table

deepakk — Wed, 15 Dec 2010 06:38:44 GMT

hi,

Sum(If(Sales > 50 and amd Sales <100,Sales))

I hope this helps.

many questions about straight table

Wed, 15 Dec 2010 07:40:37 GMT

hi Deepak Kurup ,

It does not work..

I want to filter two Salesmen's sum(Sales) . But NOT all Salesmen's sales summary which from 50 to 100.

The result should be 2 rows.

Help me. thanks

many questions about straight table

deepakk — Wed, 15 Dec 2010 08:02:08 GMT

Hi,

You can use the below method.

(aggr(sum([Sales Amount]),Customer) >= 100000 and aggr(sum([Sales Amount]),Customer) <= 600000 ,[Sales Amount]))

You need to aggregate the Sales value based on the dimension.

for eg in you staright table if you have 2 dimesnion lets say Region, Customer

then we need to check the condition in below mehthod

aggr(sum([Sales Amount]),Customer,Region) >= 100000 and aggr(sum([Sales Amount]),Customer,Region) >= 200000 then say sum(Sales Amount)

I hope you got a clear picture on the issue. Let me know if you have any queires.

many questions about straight table

Anonymous — Wed, 15 Dec 2010 08:23:54 GMT

Right now it's summing up all the "Sales" rows that are between 50 and 100.. so if Joan Callins has 10 sales that are between 50 and 100 these are summed up to a total.

If you only want the totals between 50 and 100 you can just flip the expression around to look at the totals instead of the individual rows:

If(Sum(Sales) > 50 and Sum(Sales)<100, Sum(Sales))

many questions about straight table

Wed, 15 Dec 2010 09:26:41 GMT


Deepak Kurup wrote:
if(aggr(sum([Sales Amount]),Customer) >= 100000 and aggr(sum([Sales Amount]),Customer) <= 600000 ,[Sales Amount]))


You need to aggregate the Sales value based on the dimension.
for eg in you staright table if you have 2 dimesnion lets say Region, Customer
then we need to check the condition in below mehthod
aggr(sum([Sales Amount]),Customer,Region) >= 100000 and aggr(sum([Sales Amount]),Customer,Region) >= 200000 then say sum(Sales Amount)

Thank you for your reply.

I use aggr function as you mentioned.

First table is original table. There are 9 rows in this table.

And I want to get the Salesman whose sales summary is between 50,000 and 100,000. I write a expression in calculated dimensions: =if(aggr(sum(Sales),Salesman)<100000 and aggr(sum(Sales),Salesman)>50000,Salesman).

It works! I get Joan Callins and Ingrid Hengrix 's rows. But there are 3 rows. What is the 1st row which named as "-"?

could you explaine for me, please?

many questions about straight table

deepakk — Wed, 15 Dec 2010 09:34:01 GMT

hi,

Since You have mentioned the caluculation in Dimension, there will be nulll values for the condition which doesnt satisy the mention condition.

Either you can suppress the null values in the dimension level or

Add the same expression in Expression tab.

Best method is to write it in expression.

many questions about straight table

Wed, 15 Dec 2010 10:11:25 GMT

hi,

I have understand there will be nulll values when I use caluculation dimension and I can suppress the null values in the dimension level.

But how can I Add the same expression in Expression tab? I have tried , but failed. Can you give me an example?

thanks

many questions about straight table

deepakk — Wed, 15 Dec 2010 12:00:19 GMT

hi,

Use this expression

if(aggr(sum(Sales),Salesman)<100000 and aggr(sum(Sales),Salesman)>50000,sum(Sales)).