Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Sum, Count, and Avg to include a filter, but ignore others

New to Qlik Sense, and only about a week into it.

I'm using the expressions bellow to try and limit a user's ability to filter charts on a single sheet.  These charts are from the same data set, and have overlapping information in some areas, but not all.  I'm able to use pieces of the expression bellow to either remove a user's ability to filter by Month&Year placed on Hold, or automatically filter the chart to only display those records in CurrentStatus=Canceled.  Without completely revamping my data model, how can I make this happen, and still provide users with the ability to filter the other charts with those fields.

Examples of attempts:

count({$<MonthPlacedonHold=>}{$<YearPlacedonHold=>}{<CurrentStatus={'Canceled'}>} FollowUpsCompleted)

---------------------------

Sum({$<[MonthPlacedonHold]=,[YearPlacedonHold]=>}{<CurrentStatus={'Canceled'}>} FollowUpsCompleted)

Sum({$<MonthPlacedonHold=>}{$<YearPlacedonHold=>}{<CurrentStatus={'Canceled'}>} FollowUpsCompleted)

----------------------------

avg({$<MonthPlacedonHold=>}{$<YearPlacedonHold=>}{<CurrentStatus={"$(='Canceled')"}>} DaysOpen)

1 Solution

Accepted Solutions

Re: Sum, Count, and Avg to include a filter, but ignore others

Try it like this: count({$<MonthPlacedonHold=,YearPlacedonHold=,CurrentStatus={'Canceled'}>} FollowUpsCompleted)


talk is cheap, supply exceeds demand
6 Replies

Re: Sum, Count, and Avg to include a filter, but ignore others

Try it like this: count({$<MonthPlacedonHold=,YearPlacedonHold=,CurrentStatus={'Canceled'}>} FollowUpsCompleted)


talk is cheap, supply exceeds demand
ogster1974
Honored Contributor II

Re: Sum, Count, and Avg to include a filter, but ignore others

seems to me you are doing things the right way Issac.  Your using Set Analysis to filter your fields in specific charts to bring through only the data your interested in from your data model.

heres a link to a useful set analysis guide that will help you with syntax to fix your issues in applying it.

reddys310
Honored Contributor II

Re: Sum, Count, and Avg to include a filter, but ignore others

Hi Isaac,

Any field with an empty set modifier will ignore the selection on that field. As Gysbert mentioned, if you use the above syntax it should work fine.

Not applicable

Re: Sum, Count, and Avg to include a filter, but ignore others

That works perfectly.  Much appreciated!

Not applicable

Re: Sum, Count, and Avg to include a filter, but ignore others

Thank You Andy!  This will be handy as I create some more complex visualization.

ogster1974
Honored Contributor II

Re: Sum, Count, and Avg to include a filter, but ignore others

no problem glad I could help.