topic Re: Set analysis in QlikView

Set analysis

Mon, 11 Nov 2013 12:46:47 GMT

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

Mon, 11 Nov 2013 12:57:29 GMT

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 — Mon, 11 Nov 2013 14:03:43 GMT

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

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