Set analysis with Between and excluding Nulls

[adamdavi3s]

I can't figure this one out, i've tried many combinations but obviously not the magical correct one:

I have tested the field which is len=0 when null and the chart is seeing it as a null not a missing or 0

let v_variable='count({< EndDate={">='&'$'&'(=WeekStart)<='&'$'&'(=weekend(WeekStart))"}-{''}>}DISTINCT Code)';

let v_variable='count({< EndDate={">='&'$'&'(=WeekStart)<='&'$'&'(=weekend(WeekStart))"},EndDate-={''}>}DISTINCT Code)';

let v_variable='count({< EndDate={">='&'$'&'(=WeekStart)<='&'$'&'(=weekend(WeekStart))"},EndDate-={"null()"}>}DISTINCT Code)';

[marcus_sommer]

At first I would try it without a variable to get the right logic and syntax for the expression itself (and I would consider to create the variable within the variable-overview or to keep the variables within an external file and read them with a normal load and create them with a peek-load - it avoids the whole trouble with the $-signs).

Beside them I'm not sure if you need -{''} on EndDate because this should be already excluded from your first condition on EndDate and if you really want to exclude any NULL I believe that -= {'*'} would be more suitable. Further you will need to convert and format your weekend() result because weekend returned no date else a timestamp and it should be more look like: date(floor(weekend())).

- Marcus

[adamdavi3s]

Hi Marcus,

Sorry I should have been clearer in that the expression works fine except for the fact that nulls are not being excluded.

I do however find it strange that they are being INcluded given the set:

let v_variable='count({< EndDate={">='&'$'&'(=WeekStart)<='&'$'&'(=weekend(WeekStart))"}>}DISTINCT Code)';

I'm comfortable with the way it is working and the way the dollar signs are working at the moment but yes, the variables will be moved into our meta database to be read in from there once the wireframe dash is approved for further development as is standard procedure.

Ok so (in simpler syntax) the following does not work:

count({<EndDate={">=$(=WeekStart)<=$(=weekend(WeekStart))"},EndDate=-{'*'}>}DISTINCT Code)

which results in:

=count({< EndDate={">=02/01/2017<=08/01/2017"},EndDate=-{'*'}>}DISTINCT Code)

[marcus_sommer]

What happens with this?

=count({< EndDate={">=02/01/2017<=08/01/2017"}>}DISTINCT Code)

- Marcus

[adamdavi3s]

It still includes the nulls for some reason.

There has to be something else at play here as I know that syntax should work, also if I small scale it then it works fine as in the attached.

Time to dig deeper into the data model and see what is what, picking up someone else's model here so its a pain.

[jonathandienst]

Marcus' expression should not return the null values as they will not meet the criteria. Is the seklection of the non-null values correct? Because I suspect that the entire set expression is being ignored.

[adamdavi3s]

Jonathan,

You're correct, the whole thing is being ignored which suggests and issue in the DM, I hadn't picked up on this as there is a default selection against the calendar.

Time to really pick the DM apart I think or I'm just stabbing around in the dark

[marcus_sommer]

If I understand your dummy right then there are some records without a OrderDate which indicates that the record itself isn't valid or should be flagged (valid/unvalid) or maybe the value needs to be replaced with a '#NV' or a value from the previous record or maybe from a different table.

- Marcus

[adamdavi3s]

Hi Marcus,

In reality the "missing" date is an end date which is null if there isn't one.

The original set syntax should work as these would be excluded, looking into it, it seems that they have built half a canonical date but not followed it through with the correct date bridge table, which means that the data is only linked on one date from a fact.

Basically I need to rebuild the link table and correct the missing datetype elements, then I should be able to get this working.

Agreed though, I could potentially just fill this in with a dummy date such as 01-01-9999 but as Jonathan pointed out, the whole thing isn't working so it wouldn't make any difference

[adamdavi3s]

This example is closer to reality, but again it works as expected.