Set analysis

[#1]

I need sum totals in two columns according type.

But I just want to show customers with any sales in type 1, Excluding customer sales of type 0.

I have one colum with: sum({<OrderTypeId={1}>}Total)

another colum with: sum({<OrderTypeId={0}>}Total)

Sample Data Table

Sample wrong graphic

sample correct graphics (simulated)

This is my sample load

Orders:

LOAD * INLINE [

    CustormerID, OrderTypeId, Total

    1, 0,255

    2, 1,343

    3, 0,213

    4, 1,522

    4, 1,128

  4, 0,228   

    5, 1,123

    5, 0,600

  6, 1,956

];

Thanks

[#2 · Accepted solution]

Hi,

You can use the following in your expression for type 0:

sum({$<CustormerID = {"=sum({$<OrderTypeId = {1}>} Total)>0"}, OrderTypeId={0}>}Total)

or this with aggregation (but i think it will use more resources):

sum(aggr(
if(
sum({<OrderTypeId={1}>}Total) > 0,
sum({<OrderTypeId={0}>}Total), 0)
, CustormerID))

Hope it helps

[Gysbert_Wassenaar]

Type 1: sum({<OrderTypeId={1}>}Total)

Type 0: sum({<OrderTypeId={0},CustormerID=p({<OrderTypeId={1}>}CustormerID)>}Total)