Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Variable Being ignored In My count Function

Hello All,

I'm trying to create a count statement that counts two different types of solicit codes, This year, where the donor type is Lost Donor.

The problem that i am having is The count function runs, but completely ignores my [Donor Flag Type]={'Lost Donor'} Statement. Therefore it counts each Solicit Code only by the year, and the Donor Flag Type has no affect on the count. Obviously making my results wrong.

count (

{< [Donor Flag Type]={'Lost Donor'},

Year={$(=Max(Year))},

[CONSTITUENT_SOLICITCODES.SOLICIT_CODE] = {3258}>}

Distinct [Records.FULL_NAME] )

+

count (

{< [Donor Flag Type]={'Lost Donor'},

Year={$(=Max(Year))},

[CONSTITUENT_SOLICITCODES.SOLICIT_CODE] = {3259}>}

Distinct [Records.FULL_NAME] )

1 Solution

Accepted Solutions
MVP
MVP

Re: Variable Being ignored In My count Function

The expressions looks OK, so we will need to look elsewhere for the solution.

Is the field name [Donor Flag Type] correct? QV/QS do not necessarily highlight field name misspellings.


Is [Donor Flag Type] correctly associated with the other fields in the expression? If not, selections in this field will not affect the results.


Does each value of the combination of [CONSTITUENT_SOLICITCODES.SOLICIT_CODE] and [Records.FULL_NAME] have a single value of [Donor Flag Type]?


Are you using Alternate States? If the result is in a non-default state then the selection may not apply to the result. so is ignored.(Are Alternate States are not an option in QS yet?).

7 Replies

Re: Variable Being ignored In My count Function

Hi, I don't see anything wrong with the syntax, try doing a simple count without set analysis, doing selection manually to check it the results are different, if the are differences copy the value exactly as they are selected.

It also can be:

count (

{< [Donor Flag Type]={'Lost Donor'},

Year={$(=Max(Year))},

[CONSTITUENT_SOLICITCODES.SOLICIT_CODE] = {3258,3259}>}

Distinct [Records.FULL_NAME] )

Re: Variable Being ignored In My count Function

May be missing Single colon

count ({< [Donor Flag Type]={'Lost Donor'},Year={'$(=Max(Year))'},

[CONSTITUENT_SOLICITCODES.SOLICIT_CODE] = {'3258','3259'}>}

Distinct [Records.FULL_NAME])

Or else you may describe more with the attachment. Even, you may go ahead with your following as well.

silambarasan130
Contributor II

Re: Variable Being ignored In My count Function

May this will help you,

Count (

{< [Donor Flag Type]={'Lost Donor'},

Year={"$(=Max(Year))"},

[CONSTITUENT_SOLICITCODES.SOLICIT_CODE] = {'3258','3259'}>}

Distinct [Records.FULL_NAME] )

MVP
MVP

Re: Variable Being ignored In My count Function

The expressions looks OK, so we will need to look elsewhere for the solution.

Is the field name [Donor Flag Type] correct? QV/QS do not necessarily highlight field name misspellings.


Is [Donor Flag Type] correctly associated with the other fields in the expression? If not, selections in this field will not affect the results.


Does each value of the combination of [CONSTITUENT_SOLICITCODES.SOLICIT_CODE] and [Records.FULL_NAME] have a single value of [Donor Flag Type]?


Are you using Alternate States? If the result is in a non-default state then the selection may not apply to the result. so is ignored.(Are Alternate States are not an option in QS yet?).

Not applicable

Re: Variable Being ignored In My count Function

Thanks Jonathan! I believe the problem lies within my variable [Donor Flag Type]

I used the variable in a table and it displayed correctly:

Capture.PNG

However when I try to use the dimension in a Filter pane, it states that it is an invalid dimension.

I'm trying to filter the results by selecting Lost Donor in the pane.

Here is the code for the dimension & variable I created:

if ( Count({$<Year={$(=Max(Year)-1)}>}[Gift Amount])  = 0  AND

  count({$<Year={$(=Max(Year))}>}[Gift Amount]) >= 1,

    'New Donor',

if ( Count({$<Year={$(=Max(Year)-1)}>}[Gift Amount])  >= 1 AND

  count({$<Year={$(=Max(Year))}>}[Gift Amount]) >= 1,

    'Repeat Donor',

if ( Count({$<Year={$(=Max(Year)-1)}>}[Gift Amount])  >= 1  AND

  count({$<Year={$(=Max(Year))}>}[Gift Amount]) = 0,

    'Lost Donor')))

I have no clue why the dimension would work in a table, yet i cannot select it in a filter pane.

MVP
MVP

Re: Variable Being ignored In My count Function

If you use and select an expression in a filter pane, you are actually making selections on the fields underlying the expression, rather than the expression value itself. This makes sense if you consider that the expression value only exists in the context of the object (list, table, text box, etc), whereas the field selections are global.

In your case, you have no underlying values to select.

Re: Variable Being ignored In My count Function

Hi Zachary, it will better if that field is created in script, so you will have values to selectin front-end.

Btw, in that screenshot the list is called 'Donor Type Flag', not 'Donor Flag Type'.