Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
nlamprecht
Contributor III
Contributor III

expression within modifier in set analysis

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)

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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.

View solution in original post

2 Replies
swuehl
MVP
MVP

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)