Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a date field with format D-M-YYYY hh:mm:ss
I want to count the number of employees who have september as first month of appearance
I tried count({<month(min(datefield))={9}>} employeenumber) .
Instead of {9} i tried {'sep'} and {'9'} too but I don't get the correct results.
Is it not possible to use the min and month function this way ?
I would like to refine my earlier statement a bit.
In set analysis, LHS of '='; a modifier has to be a script generated field name rather than expression.
I would restate it as:
"In set analysis, LHS of '='; a modifier has to be a script generated field name OR, an expression that is expanded to a field name."
In your example, QIX parser finds it as a string (since within single quotes) and expect it to be field name which is not there, so QIX shows result irrespective of this non-existing field. In your case, it would result current selection ($) result. And why so? HIC's explanation clarifies that; because Qlik has "forgiving" syntax rather than a 'strict' one:
"Qlik usually choose to create a forgiving syntax, since many of our users are business users and not developers. "Forgiving" as in: The software should always make a best effort attempt to understand what the expression means."
Herewith an example. It counts every employee that has a changedate in septembre, allthough that is not allways the first date.
Just to reply to your initial question: no it's not possible to use functions in a set modifier in this way.
The equal sign in a set modifier is not a comparison operator but more like a virtual assignment operator. Meaning that for this set, you temporarily stuff the values listed on the right of the equal sign (the set of field values) into the field on the left, and as a result get a set of values that is then used by the aggregation function.
In QlikView script you cannot assign values to a function call.
Try this?
=count(Distinct {<employee={"=month(min(changedate2))='sep' "}>} employee)
This did it. Checked several employees and they get the correct count.
Still don't quite understand how this works, but I am glad you solved this problem for me.
Will take a course especially for Set Analysis in October, hope I can understand these expressions better
For starters you can check this out for a very basic understanding of set analysis