QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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
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.

2 Replies
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.

MVP

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

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)