topic Re: Set Analysis overriting filter criteria in App Development

Set Analysis overriting filter criteria

alliedarmour — Tue, 22 Aug 2023 09:42:33 GMT

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?

Re: Set Analysis overriting filter criteria

Or — Tue, 22 Aug 2023 09:45:56 GMT

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?

Re: Set Analysis overriting filter criteria

agigliotti — Tue, 22 Aug 2023 09:51:53 GMT

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

Re: Set Analysis overriting filter criteria

alliedarmour — Tue, 22 Aug 2023 09:51:54 GMT

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.

Re: Set Analysis overriting filter criteria

alliedarmour — Tue, 22 Aug 2023 09:58:14 GMT

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

Re: Set Analysis overriting filter criteria

Or — Tue, 22 Aug 2023 10:00:29 GMT

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

Re: Set Analysis overriting filter criteria

alliedarmour — Tue, 22 Aug 2023 10:04:11 GMT

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?

Re: Set Analysis overriting filter criteria

marcus_sommer — Tue, 22 Aug 2023 10:35:11 GMT

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)