topic Re: how to get this count in QlikView

how to get this count

Anonymous — Wed, 27 May 2015 11:14:07 GMT

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 — Wed, 27 May 2015 11:34:28 GMT

Perhaps

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

Gysbert_Wassenaar — Wed, 27 May 2015 11:53:57 GMT

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

anbu1984 — Wed, 27 May 2015 11:54:49 GMT

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

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

ankit777 — Wed, 27 May 2015 11:55:17 GMT

You can use:

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

sushil353 — Wed, 27 May 2015 11:59:58 GMT

Hi,

Try this one:

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

HTH

Sushil

maxgro — Wed, 27 May 2015 12:28:07 GMT

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)