topic Re: expression within modifier in set analysis in QlikView

expression within modifier in set analysis

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

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)

Re: expression within modifier in set analysis

swuehl — Thu, 12 Jan 2017 16:33:21 GMT

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.

Re: expression within modifier in set analysis

sunny_talwar — Thu, 12 Jan 2017 16:33:37 GMT

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)