Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
count({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} DISTINCT TradeID)
sum({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} Volume)
count({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} DISTINCT TradeID)
sum({<TradeID=p({<Electronic={'X'}>}TradeID)*p({<Voice={'X'}>}TradeID)>} Volume)
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)
)
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?
Ah, that's a lot easier. count({<Electronic={'X'},Voice={'X'}>} distinct TradeID)
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) ?
Use '*' instead of 'X'.
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?
bump.