topic Re: function in first part of identifier set analysis in QlikView

function in first part of identifier set analysis

curiousfellow — Fri, 06 Oct 2017 06:37:51 GMT

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 ?

Re: function in first part of identifier set analysis

pradosh_thakur — Fri, 06 Oct 2017 06:42:43 GMT

in the script editor by default SET DateFormat='M/D/YYYY'; will be there but your format is diffrent

change it to SET DateFormat='D/M/YYYY'; OR SET DateFormat='D-M-YYYY';

Hopefullt it will work if your expression is correct as per your filed name .

regards

Pradosh

Re: function in first part of identifier set analysis

tresB — Fri, 06 Oct 2017 06:56:51 GMT

Try like:

count(Distinct {<datefield={"=Month(Min({1} datefield))='Sep' "}>} employeenumber)

Re: function in first part of identifier set analysis

curiousfellow — Fri, 06 Oct 2017 07:02:56 GMT

Qlik interprets my datefield correct. Tried it with date#(datefield, 'D-M-YYYY') too

However I get "Error in set modifier expression" with a red line below the { of {9}

Re: function in first part of identifier set analysis

pradosh_thakur — Fri, 06 Oct 2017 07:07:26 GMT

I would do this and try

count({<'month(min(datefield))'={9}>} employeenumber)

use distinct if needed.

regards

Pradosh

Re: function in first part of identifier set analysis

pradosh_thakur — Fri, 06 Oct 2017 07:34:00 GMT

did it work?

Re: function in first part of identifier set analysis

tresB — Fri, 06 Oct 2017 07:45:52 GMT

Pradosh,

In set analysis, LHS of '='; a modifier has to be a script generated field name rather than expression.

Re: function in first part of identifier set analysis

curiousfellow — Fri, 06 Oct 2017 07:55:28 GMT

No, this did not work either. I get a wrong result. It counts every lowest datevalue.

Re: function in first part of identifier set analysis

pradosh_thakur — Fri, 06 Oct 2017 07:56:41 GMT

hi tresesco‌

i understand that but if he is using a function it should be enclose between single quotes and it is even working for me. please correct me if i am wrong but is it a wrong practice or not at all allowed is Qlik?

Regards

Pradosh

Re: function in first part of identifier set analysis

pradosh_thakur — Fri, 06 Oct 2017 08:41:48 GMT

post a sample and lets try with that.

regards

Pradosh

Re: function in first part of identifier set analysis

tresB — Fri, 06 Oct 2017 08:47:57 GMT

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."

Re: function in first part of identifier set analysis

curiousfellow — Fri, 06 Oct 2017 10:07:13 GMT

Herewith an example. It counts every employee that has a changedate in septembre, allthough that is not allways the first date.

Re: function in first part of identifier set analysis

Peter_Cammaert — Fri, 06 Oct 2017 10:16:13 GMT

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.

Re: function in first part of identifier set analysis

tresB — Fri, 06 Oct 2017 10:18:18 GMT

Try this?

=count(Distinct {<employee={"=month(min(changedate2))='sep' "}>} employee)

Re: function in first part of identifier set analysis

curiousfellow — Fri, 06 Oct 2017 11:53:14 GMT

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

Re: function in first part of identifier set analysis

sunny_talwar — Fri, 06 Oct 2017 11:54:38 GMT

For starters you can check this out for a very basic understanding of set analysis

A Primer on Set Analysis