how to get this count

[Anonymous]

Hi All,

there is requirement where I need to calculate Orders who have only pen and pencil under that order..

for example below is my data set:

Load * inline

[

OrderID,ProductType

1,pen

1,pencil

1,paper

2,pen

2,pencil

3,rubber

3,pen

3,pencil

];

Now in the above case if I want to calculate count of orderID for only pen and pencil then it should come = 1

how to solve this problem.

Thanks in advance

[jonathandienst]

Perhaps

Count(Distinct Aggr(If(Concat(ProductType) = 'penpencil' Or Concat(ProductType) = 'pencilpen', OrderID), OrderID))

[Gysbert_Wassenaar · Accepted solution]

Try =count({<OrderID=E({<ProductType-={'pen','pencil'}>}OrderID)>}distinct OrderID)

[anbu1984]

=Count(DISTINCT {<OrderID=E({< ProductType -= {'pen','pencil'}>}) >} OrderID)

Or

=Count(DISTINCT OrderID) - Count(DISTINCT {< ProductType -= {'pen','pencil'} >} OrderID)

[ankit777]

You  can use:

==if(Concat(ProductType,'')='penpencil' or Concat(ProductType,'')='pencilpen',count(distinct OrderID))

[sushil353]

Hi,

Try this one:

=Count(Distinct OrderID)-Count(Distinct {<ProductType = e({<ProductType = {'pen','pencil'}>} ProductType)>} OrderID)

HTH

Sushil

[maxgro]

in a text box

=sum(aggr(if(count({$ <ProductType={pen,pencil}>} distinct ProductType)=count(DISTINCT ProductType),1,0),OrderID))

in a chart with order id as dimension

if(count({$ <ProductType={pen,pencil}>} distinct ProductType)=count(DISTINCT ProductType),1,0)