topic Re: count distinct when all value in field x are null in QlikView

count distinct when all value in field x are null

curiousfellow — Thu, 17 Sep 2020 13:56:27 GMT

I have a table in which there are for every employee several records.

I a date field there can be a date-value but it can be empty to.

Now I want to distinct count all employees for which all datefields are empty. I cannot get to the right expression.

I tried something like =sum(if (agrr(sum(date-field),employee)= 0 count(distinct employee) ) but that does not give any result.

There are more conditions to add like date(start) < addyears(now(),-1) but when you could be so kind to give me a hint I can do the rest by my self.

Re: count distinct when all value in field x are null

sunny_talwar — Thu, 17 Sep 2020 14:09:52 GMT

Try this

Count(DISTINCT {<employee = {"=Len(Trim([date-field])) = 0"}>} employee)

Re: count distinct when all value in field x are null

curiousfellow — Fri, 18 Sep 2020 06:26:33 GMT

Thank you for your quick answer, unfortunately it does not give the correct result. This counts the employee for which one of the date fields is empty, but it should only count when ALL date fields are empty for that employee.

Re: count distinct when all value in field x are null

tresB — Fri, 18 Sep 2020 06:43:06 GMT

@curiousfellow ,

You are probably talking about all VALUES in a date field to be empty. If so, try like:

Count(DISTINCT {<employee = {"=Max(Len(Trim([date-field]))) = 0"}>} employee)

Re: count distinct when all value in field x are null

curiousfellow — Fri, 18 Sep 2020 11:09:37 GMT

I changed your suggestion to count(distinct {<employee={"=len(aggr(max({Inschrijvingen.examendatum),employee))=0"}>} employee) and now it works. don't know if it was neceassary because there was another error caused by the sequence of the modifiers, which I think is very strange. I will open a new topic for that.