Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

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.

1 Reply
Not applicable
Author

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