Using set analysis aggregated by another field

[jwheatonumich]

I have three fields in my dataset:

• Customer
• SaleID
• Revenue

I want to create an table with Revenue as an expression and SalesID for a dimension. I only want the table to display rows for sales where a customer's total Revenue is not equal to zero.

My initial attempt led me to the following:

=sum({$<Revenue-={'0'}>}Revenue)

The issue with the above formula is that it checks whether a SalesID has zero revenue, rather than checking whether a Customer does.

Is it possible to aggregate the set analysis by Customer? Or to achieve what I want some other way?

[Anil_Babu_Samineni]

May be this?

=sum({$<Revenue-={"=Customer = 0"}>}Revenue)

[sunny_talwar]

May be try this

=Sum({$<Customer = {"=Sum(Revenue) <> 0"}>} Revenue)

[jwheatonumich]

This results in a value of zero for revenue on every row.

[sunny_talwar]

Would you be able to share a sample where you have this problem?

[jwheatonumich]

This does not filter out any of the results.

It looks like this is filtering out any results where Customer is equal to zero, rather than where Revenue by Customer is zero.

[jwheatonumich · Accepted solution]

I ended up solving the problem by doing the following:

Loading a new table with a flag for Customers with nonzero revenue:

Customer_Revenue_Flag:

LOAD Customer,

if(Revenue <>0,1,0) as Customer_Revenue_Flag

From [...] (qvd);

The new table was joined to my original table by the customer field since they shared a name, allowing me to use the following set analysis to only include customers with nonzero revenue:

=sum({$<Customer_Revenue_Flag-={'0'}>}Revenue)