Aggregation problem

[sifatnabil]

Hi,

I have a table structure like this:

PrimaryKey	TradeID	Electronic	Voice	Volume
123	11	(blank)	X	10
124	11	X	(blank)	5
125	11	(blank)	X	10

What I want do is:

For each unique TradeID,

if Electronic contains X in any row AND if Voice contains X in any row,

count(distinct TradeID) and

sum(Volume)

I know this involves some sort of aggregation and I tried this in a textbox:

count(distinct if(len(Electronic)>0 and len(Voice)>0, TradeID))

But this returns an error.

[Gysbert_Wassenaar · Accepted solution]

count({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} DISTINCT TradeID)

sum({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} Volume)

[JonnyPoole]

Try something like this:

It checks if 'X' is in any row of Electronic and any row of Voice for each TradeID. 

If its in both at least one time, then count the tradeID otherwise don't count the tradeId

=count( DISTINCT 

  if(

  aggr(SubStringCount(Concat(DISTINCT Electronic),'X'),TradeID)

  and

  aggr(SubStringCount(Concat(DISTINCT Voice),'X'),TradeID)

  , TradeID)

)

[sifatnabil]

Thanks Gysbert, sorry I should actually correct this line in my question:

if Electronic contains X in any row AND if Voice contains X in any row,

to

if Electronic is not blank in some rows AND if Voice is not blank in some rows

That's why I used concat(len(Electronic)>0 ..

Could you incorporate len into your solution?

[Gysbert_Wassenaar]

Ah, that's a lot easier. count({<Electronic={'X'},Voice={'X'}>} distinct TradeID)

[sifatnabil]

Sorry, the reason I can't select X is because X can be any string, like ABC or DVD, how can I amend it like count({<Electronic={'Any value except blank'},Voice={'Any value except blank'}>} distinct TradeID) ?

[Gysbert_Wassenaar]

Use '*' instead of 'X'.

[sifatnabil]

Thanks, actually your original answer worked but with '*'. Thanks! And what if I wanted the inverse? i.e. can I use count({<TradeID=p({<Electronic-={'*'}>}TradeID)*p({<Voice-={'*'}>}TradeID)>} DISTINCT TradeID) ?

Set analysis doesn't seem to allow -=

Also, what's the best way to display this in chart format if I want the dimension to be date or any other field?

[sifatnabil]

bump.