Subset Function: Unique combinations of two fields?

August 17, 2012 7:37AM

Hi - When using the formula: SUBSET_UNIQUECOUNT([ProjectId],subset([locplaats])).

I get the following message:

Cannot use field value references. This formula is evaluated in the context of multiple records, without any specific individual record. You must identify the summary function, such as: SUBSET_SUM([locplaats]) or SUBSET_MEAN([locplaats]). If using SUBSET([locplaats]), you must specify at least 2 arguments, such as SUBSET([locplaats], "Required value").

I think this is a bug, or I am doing something completely wrong. Please advise... Thanks! (Running 2.7 b413)
Regards, Alexander

steve · August 17, 2012 7:45AM

You are using this in the Content View or the Bar/line view?

Please explain your formula in English. For example:

"Give me the number of unique "ProjectId" values within the subset where ..."

August 17, 2012 7:50AM

Content view

"Give me the number of unique projects [Projectid] whitin the subset were the location [locplaats] is identical."

steve · August 18, 2012 8:42AM

Identical to what?

August 20, 2012 3:05AM

In my dataset I have 17 different values for [locplaats]. I want to calculate the number of unique projects [ProjectID], for every value in [locplaats].

So how many projects do I have where [Locplaats] is identical (in other words: one value)

paola · August 20, 2012 11:23AM

Formula = SUBSET_UNIQUECOUNT([ProjectID],SUBSET([Locplaats]))
will return 1 against each Locplaats, if there is only one ProjectID per Locplaats.
For 17 different values, each Locplaats will have 1 in the formula result field.

If you wish to find out how many Locplaats have 1 ProjectID against them:
SUBSET_UNIQUECOUNT([Locplaats],SUBSET([Formula],"1","="))
for all different values, in this case you will have 17 in each row.

August 20, 2012 11:26AM

Hi Paolo,

Thanks for your comment, yet this is not what I mean. It is difficult to explain... can we have short call, so that I can explain the issue? Thanks!

paola · August 20, 2012 11:28AM

Hi Alexander, sure - please give me a call on our support number. 207 351 4646 and add 0044 if calling from outside UK.

paola · August 20, 2012 1:35PM

Hi Alexander,
You can do this in two steps:
- Introduce field to concatenate the two values Field C= [Field A]+” “+[Field B]
- In the Content view use formula that will return a single value result
SUBSET_UNIQUECOUNT([Field C])
This formula will count total number of unique combinations of fields A and B.

Welcome!

Categories

Ideas Parade

Tagged