Set Analysis overriting filter criteria

[alliedarmour]

Hey,

once again (and hopefully the last time for now) a question regarding set analysis.

I have a really simple set expression, where I only want to include records with two matching dates (which are in seperate tables, but associated by the key field):

Sum({$<Leistungskatalog = {"*"}, FallBewegungKey = {"=Leistungsdatum = BewegungDatum"}>} AnzahlLeistung)

The values which are generated by this expressions are correct, however if I select a value for the Leistungskatalog field, it still uses the filter, although I explicitly set it to {"*"}.

If I delete the second condition, it's working fine and the selection in this field is ignored:

Sum({$<Leistungskatalog = {"*"}>} AnzahlLeistung)

Any ideas how to get this working? 

[Or]

This is how it's supposed to work - a defined value is meant to override any selections. You can use *= to get the intersection of the full set ("*") with the selection, if that's what you're after here?

The second part with the dates doesn't maje much sense to me. I think it would evaluate to either true of false since you're checking of two dates are identical. Is FallBewegungKey a true/false field?

[agigliotti]

Hi @alliedarmour ,

I think you should ignore the Leistungskatalog field selections as below:

Sum({$<Leistungskatalog, Leistungskatalog = {"*"}, FallBewegungKey = {"=Leistungsdatum = BewegungDatum"}>} AnzahlLeistung)

I hope it can helps.

Best Regards

[alliedarmour]

Hey @Or ,

1. Yeah. but it doesn't override the current selection if I add the date comparison. In other words If I use the first Set expression and then select a value for Leistungskatalog, it applies this filter and "overrides" the * selection with the selected value.

2. No, it's not a true/false field - I want to include all records (it's like an ID field) which have matching dates in those two date fields. 

[alliedarmour]

Thanks for your reply, but still not working - seems to be the date comparison somehow breaks it.

[Or]

There is no date comparison... your set isn't checking a range, it's comparing the two dates?

[alliedarmour]

I'm relatviely new to Qlik - so I want to get those keys / records which have matching date values in the two fields, If those match, add the record to the set - is this the wrong way?

[marcus_sommer]

What you are trying to do with:

=Leistungsdatum = BewegungDatum

isn't a set analysis else a (separate) if-loop which of course will consider the set selections.

I suggest to re-think the used data-model if it's really suitable for your view-requirements. Officially recommended is to apply a star-scheme with a single-fact table and n dimension-tables.

By having multiple date-fields you may also need multiple calendars and/or also a canonical calendar and/or as-of-tables and/or including various (pre-) calculation within the facts or the dimensions, for example subtracting the date-values against each other to create their differences as offset-values and the above expression may look like:

Sum({$<Leistungskatalog = {"*"}, DateOffset = {0}>} AnzahlLeistung)