topic Re: Using set analysis aggregated by another field in QlikView

Using set analysis aggregated by another field

jwheatonumich — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Using set analysis aggregated by another field

Anil_Babu_Samineni — Tue, 08 May 2018 23:57:13 GMT

May be this?

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

Re: Using set analysis aggregated by another field

sunny_talwar — Wed, 09 May 2018 02:37:35 GMT

May be try this

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

Re: Using set analysis aggregated by another field

jwheatonumich — Wed, 09 May 2018 12:41:49 GMT

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

Re: Using set analysis aggregated by another field

sunny_talwar — Wed, 09 May 2018 12:45:29 GMT

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

Re: Using set analysis aggregated by another field

jwheatonumich — Wed, 09 May 2018 12:55:19 GMT

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.

Re: Using set analysis aggregated by another field

jwheatonumich — Wed, 09 May 2018 15:58:23 GMT

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)