Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
= 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
= 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?
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