1 Reply Latest reply: Oct 28, 2016 3:02 PM by Shaun Fleishman RSS

    Sum expression based on filters

      Hi, I'm trying to figure out how I can sum a field based on the selected filters on my sheet.  I'm working with a dataset of work orders and I have a count based on when the work order was created and filtered by the currently selected year/month which works fine but now that the data is filtered by the create date I need to get a count of work orders that were closed in the same range.  There can be work orders that were opened in a previous month that currently isn't selected but were closed the next month.  These records would be excluded because of the filter but I need to get a count of those.


      e.g. Work order #1

      Created 8/1/2016

      Closed 9/1/2016


      Filter #1 year of create date = 2016, filter #2 month of create date = Oct, Sep


      My dataset would not include this record in my table object since it was opened before September but I still need to include it in the closed count since that does fall in my filter selection.


      I found this link here but I couldn't get it to work with Qlik Sense:



      Here's my vSelectedYear variable:

      If(GetSelectedCount([CreatedDate.autoCalendar.Year])> 0, concat (DISTINCT [CreatedDate.autoCalendar.Year], ','), '*' )


      Here's my vSelectedMonth variable:

      If(GetSelectedCount([CreatedDate.autoCalendar.Month])> 0, concat (DISTINCT [CreatedDate.autoCalendar.Month], ','), '*' )


      Here's the measure I'm attempting to test with (the closed field is just a 1 or 0 that I added to my script which is set to 1 if a close date is set or not:



      I'm have a text & image object set for $(vSelectedYear) and $(vSelectedMonth) to the right of the filters for testing which appears to be working, but the last text & image object using the expression "Sum({1<[ClosedDate.autoCalendar.Year]={$(vSelectedYear)}>}[Closed])" doesn't seem to work.  Any help would be appreciated.



      It does work as expected if I manually replace $(vSelectedYear) with "2016","2015" if i wanted to include that year as well.

        • Re: Sum expression based on filters

          I was able to get this to work based on another thread I found:


          sum( {1 <


              [ClosedDate.autoCalendar.Year]={$(=chr(34) & concat(distinct [CreatedDate.autoCalendar.Year], chr(34)&','&chr(34)) & chr(34))},

              [ClosedDate.autoCalendar.Month]={$(=chr(34) & concat(distinct [CreatedDate.autoCalendar.Month], chr(34)&','&chr(34)) & chr(34))}


              >} [Closed])