Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Within the current selection, I'm trying to count everybody who's spouse is also selected. Here's what I've got:
= Count({$
[Relation Related ID] = {$[Relation ID]}>
}
distinct [Relation ID])
The part in bold {$[Gifts ID]} is what I know is wrong. I want that to represent all of the Relation ID's of the current selection. I know if i were to say [Relation Related ID] = [Relation ID] it would look for rows where the two fields were equal. What I want is rows where the value in [Relation Related ID] can be found anywhere in [Relation ID] of the current selection. Does that make sense?
Thanks very much for any help you can offer.
Ok,
I don't remember if set analysis in v8.5 allows for example the following syntax:
Count({< Field2 = {"$(=chr(39) & Concat(Field3, chr(39) & ',' & chr(39)) & chr(39))"} >} Field1)
Or something like that, since Concat() allows to get a comma separated list of all possible values in Field3. Those chr(39) are the single quote character, required when the values are literals (strings) so the Concat() function in a text box should return
A','B','C
Without the first and last single quotes, that I'm adding in the set analysis modifier.
It may be worth checking.
Hello,
Maybe using P() function in set analysis?
Count({< [Relation Related ID] = P([Relation ID]) >} distinct [Relation ID])
That would read count any distinct value of "Relation ID" where "Relation Related ID" equals to any of the possible values -not excluded- of "Relation ID".
Is that what you are looking for?
Thanks for the quick response Miguel. That's exactly what I'm looking for, the problem is that we're stuck with Qlikview 8.5 and as I understand it the p{} function hasn't been introduced yet.
I'm tempted to give up, but I want to think that it must be possible given that you can easily compare multiple sets with each other.
Ok,
I don't remember if set analysis in v8.5 allows for example the following syntax:
Count({< Field2 = {"$(=chr(39) & Concat(Field3, chr(39) & ',' & chr(39)) & chr(39))"} >} Field1)
Or something like that, since Concat() allows to get a comma separated list of all possible values in Field3. Those chr(39) are the single quote character, required when the values are literals (strings) so the Concat() function in a text box should return
A','B','C
Without the first and last single quotes, that I'm adding in the set analysis modifier.
It may be worth checking.
Ok,
I don't remember if set analysis in v8.5 allows for example the following syntax:
Count({< Field2 = {"$(=chr(39) & Concat(Field3, chr(39) & ',' & chr(39)) & chr(39))"} >} Field1)
Or something like that, since Concat() allows to get a comma separated list of all possible values in Field3. Those chr(39) are the single quote character, required when the values are literals (strings) so the Concat() function in a text box should return
A','B','C
Without the first and last single quotes, that I'm adding in the set analysis modifier.
It may be worth checking.
I'll give this a shot and see if I can make it work, it seems like it should. Thanks!
After removing the quotes this did the trick. Thanks again!