Help with weird Qliksense behaviour

[akash_durai1507]

Im experiencing a weird situation in qliksense where,
When I select a condition in my table,

=YearEnd>=NetDueDate

 Qliksense always returns both positive and negative cases.(Video attached.)
The data is not getting filtered.

To give you a background,
I'm using an As-Of table(Report_Calendar) to calculate rolling sum of Amounts.
To calculate overdues, I'm comparing 
1. YearEnd field from As-of table with NetDueDate in my fact(AccountsReceivable).
2. Clearing Date from my fact to YearEnd field in As-of table.

These conditions when used in set analysis were not returning expected results.
So I tried running the conditions individually and noticed this weird behaviour.

My data model looks like below,

 Any help would be appreciated.

TIA,

[rubenmarin]

Hi, the As-Of table is not shown in your date model screenshot, I would try to create a table in a sheet with the fields named in your post and check than the relations created are the ones you expect.

[akash_durai1507]

Hi rubenmarin,

Report_calendar is my as-of table connected to the fact via Bridge_Table (highlighted in red.)

I've tried adding all the fields required for analysis in a table. It looks ok tho.

I cannot understand the behaviour of qlik that when i compare the field from Report_Calendar as-of table with a field from the fact table, qliksense selects even values not satifying the comparison.

I've tried showing this in the gif attached below.

Please take a look

Thanks

[marcus_sommer]

The as-of-table is linked against a certain date-field - comparing them against other period-information may not be valid and/or requiring other syntax, like: min/max() if the other side has more a single value in regard to the call-context.

Without knowing your data-set and requirements in more depths it's difficult to give more detailed hints. I assume that I would try to pre-calculate various helpful information within the data-model, for example fields which the relevant min/max dates, year/month-end dates, the offset of the various dates to each other and/or also to today and similar stuff ...

[akash_durai1507]

hi marcus,
Thanks for the reply.



My requirement is as follows,
1.Calculate rolling sum of Amounts
    --->I have created a As-Of table for this. So selecting a value (like 2024-Jan) on As-Of table sums all the amounts until 2024-Jan.

2. Compare NetDueDate, Clearing Date with As-Of Date to find Overdues.
   -->This is where the problem is.
        the current table set up is - fact table --->bridge table --> As-Of Table
        the fact and bridge table is connected using Posting Date.
        Comparing the date from As-Of table with NetDue and Clearing date is not giving expected results.

[marcus_sommer]

Multiple date-fields are always difficult - they may require n calendars and maybe also n as-of-tables also their combination - whereby it may further necessary to extend them with appropriate information, like their exists in the other parts and/or their offsets ...

In many scenarios it's not mandatory necessary to use n date-fields because they might be merged into a single date + an extra information of which kind of date it is. The matter relates to the relationship of the data. For example: sales + budget + forecast are in general the same data - just the view-direction is different. Similar is it by data like: order + billing + shipping - here is it also only a different point of the journey. Therefore in many cases the data-model could be mainly a star-scheme and a single calendar + as-of-table may enough.

Nevertheless the above mentioned offset-calculations might be applicable for your view. This means something like:

PostingDate - ClearingDate as Flag_X
NetDueDate - ClearingDate as Flag_Y
PostingDate - NetDueDate as Flag_Z

and these flags might be grouped/scored in regard > n days or monthend/yearend ... and those information could be then used as conditions or selections.