expression within modifier in set analysis

[Anonymous]

Hello! Thank you so much for taking the time to read my post and potentially help me figure out how to make this work.

I have a list of toys sold at various store locations (these stores are represented with numbers) and then delivered to houses that have a designated number field.  I would like to subtract these two fields and then count how many toys were sold that were under a certain difference.  (sorry - I know that's complicated so I put an example below).

Field:        Store #   Toys sold      to what house #       (pull back?)

  A                 4               7                    10                              (no)

   B                 3               10                    50                            (yes)

   C                 4                 2                      45                         (yes)

   D                6                 50                    3                              (no)

So I would want to count the number of toys sold in rows B and C. The set expression I think I should use is below, but it doesn't work.  Help?

=count( {< fabs(store# - house#) = {">25"} >} toys_sold_CNT)

[swuehl · Accepted solution]

You can only make selections in fields, same way you can only use field names from your model on the left of an equal sign in the set modifier:

{<FIELDNAME = {">25"} >}

Not sure how your data model looks like, try maybe something like

=Count({<Field = {"= fabs(store# - house#) >25"}>} toys_sold_CNT)

where Field is the field name in your sample, with values A,B,C,D.

[sunny_talwar]

I would create a new field in the script to do this

LOAD Field,

          Store#,

          [Toys sold],

          [to what house #],

          toys_sold_CNT,

         fabs(Store# - [to what house #]) as NewFieldName

FROM ....

and then use this:

=Count({<NewFieldName = {'>25'}>} toys_sold_CNT)