Result of Expression Not Summing Correctly

[kdstrin1]

I am trying to build an expression that determines the number of elapsed business days in the month. The Calendar.BusinessDayFlg field is either 1 or 0 for business day Y/N. The vMonthNumber is a variable to tie to current month on a fiscal year basis. The expression below gives me the OK message, yet the result of the expression is 0, which is not accurate. Running this on April 29, it should give me a result of 20 (excluding weekends, no holidays in April). Attached is an output of dimensions to show how the data is setup.

Sum({$<YearDimension={'CurrentYear'},FiscalMonthNBR={$(vMonthNumber)},EncounterDate={'<$(=DATE(Today(),'YYYY-MM-DD'))'}>}[Calendar.businessdayflg])

[Oleg_Troyansky]

Hi there,

I believe the expression is almost there, with a very subtle mistake... In your filter for EncounterDate, you are enclosing the whole condition in single quotes, which means that you are trying to select that whole text 

'<$(=DATE(Today(),'YYYY-MM-DD'))' as a value in the Encounter Date field. I believe the correct syntax should be the following (swap the < and the ' 😞

EncounterDate={<'$(=DATE(Today(),'YYYY-MM-DD'))'}

This would work if your date is formatted as 'YYYY-MM-DD'. To be free from date formatting issue, you could use the advanced (expression) search, with double quotes and the equals sign:

EncounterDate={"=EncounterDate <Today()"} 

Allow me to invite you to my session on Set Analysis at the Masters Summit for Qlik, where I teach advanced uses of Set Analysis, along with many other advanced development techniques. Coming this fall to Hamburg, Germany! 

[kdstrin1 · Accepted solution]

Thank you so much. It wound up being a mix of both of your suggestions. I replaced the single quotes with double quotes;

Sum({$<YearDimension={'CurrentYear'},FiscalMonthNBR={$(vMonthNumber)},EncounterDate={"<$(=DATE(Today(),'YYYY-MM-DD'))"}>}[Calendar.businessdayflg])

Thanks again!