Simple set analysis

[#1]

Hi,

I'm trying to return a value based on the current selection (0910) in a list-box.

So, for example if a user selects Ac=0910, I want to return 0809 = 0910-101. ie. Ac-101.

or if the user selects 0809, then it must return 0708, etc etc.....

It will always be 101 less than current selection.

Here is the start of my expression :

count( {$<Ac = {$(=Ac-'101')}>} distinct [Prog] )

I'm sure someone has done a similar expression before.

Much appreciated,

Xena

[#2]

Hello Xena

I think I would do the AC part in a variable, so declare a variable and make the content of the variable as follows:

=GetFieldSelections("AC") - 101

Let's assume you call your variable ACMod, then your set analysis expression would be:

=Count({$<Ac={$(ACMod)}>} Distinct [Prog] )

[#3]

Did you try

count( {$<Ac = {'$(=Ac-101)'}>} distinct [Prog] ) ?

[#4]

Hi Nigel, Spastor,

Both expressions return a value of 0.

I did test the variable in a listbox, but that is correct.

Not sure what else could be the problem?

[#5]

The problem is that you are selecting a value (for example 903) in the field AC.

Therefore your current selection is made on AC=903.

The Set analysis will take the values inside your selection where AC=903.

What I would do is creating a Table with two fields: First field is AC ( renamed as AC_REFERENCE) and the second one is NUMBER_AC_TO_SELECT which is AC-101.

Then you base your Set analysis based on AC={s(=only(NUMBER_AC_TO_SELECT))}

Rgds,

Sébastien

[#6]

Spastor,

Thanks for your quick response.

Yes, I came up with that solution which seems to work.

I figured it had something to do with the 0 in front of AC.

I just created a quick ref/mapping table, which worked.

Thanks!