Sum expression based on filters

Fri, 28 Oct 2016 16:25:31 GMT

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.

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

Fri, 28 Oct 2016 19:02:35 GMT

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

topic Re: Sum expression based on filters in App Development

Sum expression based on filters

Re: Sum expression based on filters