Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])