topic Re: Need help with Data in Set Analysis in QlikView

Need help with Data in Set Analysis

MH_Newbie — Wed, 25 Nov 2020 16:16:04 GMT

Hey!

I need help with some Data in Set analysis.
I want to see all machines which are currently in a contract. Some of them have a termination date (Field "[Kündigungsdatum Vertragsposition]") already but most of them not.
The conditions are:

[Kündigungsdatum Vertragsposition] is null OR [Kündigungsdatum Vertragsposition] >= today()

With my following Set Analysis I just get every machine with a termination date greater than today:

= num(
sum( {$<[aktive Vertragsposition]={1}, isFiktivesModell={0}, virtuellePosition={0}, [Kündigungsdatum Vertragsposition]={'>=$(=Date(Today()))'} + {''} >} [Zähler (Gerät)])
, '#.##0')

How can I get the other machines without any termination date?

Re: Need help with Data in Set Analysis

sasiparupudi1 — Wed, 03 Jul 2019 19:17:44 GMT

= num( sum( {$<[aktive Vertragsposition]={1}, isFiktivesModell={0}, virtuellePosition={0}, [Kündigungsdatum Vertragsposition]={'>=$(=Date(Today()))'} ,RowID={"=Len([Kündigungsdatum Vertragsposition])=0"}>} [Zähler (Gerät)])

where RowID is your primary key value in the table.

Otherwise, use if condition

Re: Need help with Data in Set Analysis

MH_Newbie — Fri, 05 Jul 2019 08:47:49 GMT

Unfortunately this doesn't work. The result is the same...

What could be a possible if-condition?

Re: Need help with Data in Set Analysis

marcus_sommer — Fri, 05 Jul 2019 11:12:19 GMT

To fetch NULL within the set analysis is difficult and couldn't be done on the field itself else it will need an indirect set analysis - therefore the suggestion from sasiparupudi1 to use another field for it. But in the way it's applied the conditions have an AND connection which could be never true - else it needs an OR connection.

I think it should be possible to develop such an approach but I would rather try to reverse the logic with something like:

... [Kündigungsdatum Vertragsposition] -= {"<$(=Date(Today()))"} ...

Maybe even better as that might be to fill these NULL's with real values within the script - in your case maybe with the 31.12.9999. With it those records will be selectable again and your statement of:

... [Kündigungsdatum Vertragsposition] = {">=$(=Date(Today()))"} ...

should work like expected. In other cases those dummy-values could be directly accessed to in- or exclude them - and should they really disturb anything you could apply it on an extra field.

- Marcus