Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Need help with Data in Set Analysis

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?

3 Replies
Highlighted
Master III
Master III

= 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

Highlighted
Contributor II
Contributor II

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

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

What could be a possible if-condition?

Highlighted
MVP & Luminary
MVP & Luminary

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