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

    Sum expression based on filters

    Shaun Fleishman

      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:

      https://www.resultdata.com/qlikview-set-analysis-ignore-all-filters-but-these-two/

       

      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:

      Sum({1<[ClosedDate.autoCalendar.Year]={$(vSelectedYear)}>}[Closed])

       

      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.

      Untitled.png

       

      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
          Shaun Fleishman

          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])