Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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])