6 Replies Latest reply: Dec 29, 2015 11:52 AM by Andy Weir RSS

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

    Isaac Rosser

      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)