many questions about straight table

[#1]

I have such a table.

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

[deepakk]

hi,

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

I hope this helps.

[#3]

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

[deepakk · Accepted solution]

Hi,

You can use the below method.

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)

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

[Anonymous]

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))

[#6]

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?

[deepakk]

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.

[#8]

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

[deepakk]

hi,

Use this expression

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