[Hard] Set analysis with multiple dates by month

[t_donnet]

Hello,

I've difficulty to have corrects results for a table chart aggreagate by end of months date and the max date of the current month.
In comparaison the KPI chart display the correct result for one date selected.

The application have 'Always one selected value' on the date field.

The KPI measure :

Count(Distinct
{< [DimKundenstamm.ValidFrom]={"<=$(vDateMax)"}, [DimKundenstamm.ValidTo]={">=$(vDateMax)"}
, [DimKundenstamm.Eroeffnungsdatum]={">=$(vDateYearStart)<=$(vDateMax)"}
, [DimKundenstamm.IsAufhebungsdatum]={1}, Date=

, %KundenstammId = P({< [DimKontostamm.ValidFrom]={"<=$(vDateMax)"}, [DimKontostamm.ValidTo]={">=$(vDateMax)"}
, [DimKontostamm.IsAufhebungsdatum]={1}, Date= >})

>} [DimKundenstamm.Kundenlaufnummer])

vDateMax = The one date selected
vDateYearStart = The first date of the year from the vDateMax

The measure contains two types of between:

1. A between in the same field
2. A between on two fields 

The table chart measure:

Count(Distinct
{< [DimKundenstamm.ValidFrom]=$(vDateSetMin), [DimKundenstamm.ValidTo]=$(vDateSetMax)
, [DimKundenstamm.Eroeffnungsdatum]={">=$(vDateYearStart)<=$(vDateMax)"}
, [DimKundenstamm.IsAufhebungsdatum]={1}, Date=

, %KundenstammId = P({< [DimKontostamm.ValidFrom]=$(vDateSetMin), [DimKontostamm.ValidTo]=$(vDateSetMax)
, [DimKontostamm.IsAufhebungsdatum]={1}, Date= >})

>} [DimKundenstamm.Kundenlaufnummer])

vDateSetMin =  '<=' +  contains all end of month date to the date selected

vDateSetMax =  '>=' + contains all end of month date to the date selected

The KPI result is always correct. The measure inteprete only one date in the set analysis.

The table result is partially correct. This measure have multiple dates in the set analysis. 

I provide a sample app (link, file to big to attach)and a QVD with results.

Thanks for your help.
Théo

[marcus_sommer]

I'm not sure if I understand your issue right but the following things to ... [DimKundenstamm.ValidFrom]=$(vDateSetMin) ... are noticeable:

• the variable must return a whole valid string looking like {'value1', 'value2'}
• the values must have the same format like the values from ValidFrom
• the values will be the same for each row in your table - because set analysis worked on a colum-level not a row-level - if you need a row-level you need to use an if-loop

- Marcus

[t_donnet]

Hi Marcus,

thanks for your reply.

the variable $(vDateSetMin) contains an automatic structure with values in good format
->  {"<=31.01.2021"}+{"<=28.02.2021"}+{"<=31.03.2021"}+{"<=30.04.2021"} ... 

Can you give me more info with the if-loop? and an example?
I'll try this way.

Thanks again
Théo

[marcus_sommer]

I'm not sure if this is really a valid syntax - means either the + operator in combination with the <= comparing between the values as well as the values itself - just checking if ... {"<='31.01.2021'"} ... makes a difference.

Beside this I would say it's logically rather adverse to make the check against multiple values if you used <= because taking just the last list-parameter would be enough.

- Marcus

[t_donnet]

Hi Marcus, 

That's all the thing: How to "translate" the KPI measure (with one date) to a measure that contains multiple dates.

I try to add simple quote around the date. It didn't go well, all results are 0.

Don't hesite to try out the sample app.

Regards,
Théo

[marcus_sommer]

I think it's more a logically mistake. The usual approach is to compare a date against:

• a list of dates - date = {'date1', 'date2', ...}
• greater/lesser dates - date = {"<=date"}
• between dates - date = {">=date1<=date2"}
• multiple date-ranges - date = {">=date1<=date2"} + {">=date3<=date4"}

Your attempt is different to it and beside possible syntax-issues it's IMO not logically correct. I assume that you want - at least for the moment - remain by your general object/expression-logic so that I think you need another variable which contains just the last parameter of your variable-string.

- Marcus

[t_donnet]

Hi Marcus,

I can understand that you stand for a logically mistake,  but it isnt. 
In bank companies, there use frequently a start date and an end date for a record on most of tables.
That's why it's complicate to find a solution. 

For the moment, I haven't a answer. Still struggling...

How do you see from your perpective to find a good approch?

Théo

[marcus_sommer]

I never tried to compare multiple dates with a lesser/equal operator and linked with an OR condition within a set analysis or within another construct. Like above mentioned I think the variable should be better adjusted to a single date or startdate + enddate - this doesn't mean to change the included values of your condition else just to write the condition differently. Beside this I think you could with rather small efforts test if your logic and syntax worked - just by replacing the variable with two hard-coded dates and/or to trying to adjust it with another operators or other syntax-methods - just by playing a bit with them.

Further you may remove potential formatting/conversion issues by replacing the formatted dates with a pure numeric value - it's a general recommendation for all matchings and calculations.

Beside this it looked that you here aren't really comparing dates else months. Therefore it might be possible to change the logic to these periods - for example year(date) * 12 + month(date) returned a continuous number which might be then queried with <= or <= >= or just listed like … Field = {24001,24002,…} …

Before all of this I suggest to review the datamodel - is it really suitable to handle the multiple date-fields? Ideally none of such measures are needed to calculate/display the wanted views else more simple aggregations like sum(Field) are enough and the users selects what they want to see. In your case this might not be trivial but often there are possibilities to transfer a big part of the logic into the script. For this you may consider to:

• apply intervalmatches to your start- and enddates to relate them with a single date
• using of multiple master-calendar
• combining the master-calendars into a canonical calendar
• adding further as-of-tables
• creating of multiple flags
• using of island-tables for the selection - enables for example constructs like: … date = p(IslandDate) …

More to the meant considerations could you find here:

How to use - Master-Calendar and Date-Values - Qlik Community - 1495741

IntervalMatch - Qlik Community - 1464547

The As-Of Table - Qlik Community - 1466130

- Marcus

[t_donnet]

Thanks for your reply.

The data model is already correct.

I tried to apply intervalmatch. A QVD with 200MB after intervalmatch transformation is store at 5.5GB !!!
The table contains only two years data.  
So there is an interval date table to avoid this huge QVD.

Let's try this in the most simple way. Here's the data model in use.
(Dotted tables and associations are possible ways)

ValidFrom ≠ KontoValidFrom
ValidTo ≠ KontoValidTo

 KPI
Count(Distinct
{< ValidFrom={"<=$(vDateMax)"}, ValidTo={">=$(vDateMax)"}
, %Konto= P({< Konto.ValidFrom={"<=$(vDateMax)"}, Konto.ValidTo={">=$(vDateMax)"}, %Date= >} )
>} %Client)

vDateMax = Max(Date) = Only one date selected in MasterCalendar

The KPI result is always correct. The measure inteprete only one date in the set analysis.

How would you do with one measure by end of month?

Interpretation of that table chart:

Month	Measure
January	Count(Distinct {< ValidFrom={"<=31.01.2021"}, ValidTo={">=31.01.2021"} , %Konto= P({< Konto.ValidFrom={"<=31.01.2021"}, Konto.ValidTo={">=31.01.2021"}, %Date= >} ) >} %Client)
February	Count(Distinct {< ValidFrom={"<=28.02.2021"}, ValidTo={">=28.02.2021"} , %Konto= P({< Konto.ValidFrom={"<=28.02.2021"}, Konto.ValidTo={">=28.02.2021"}, %Date= >} ) >} %Client)
March	Count(Distinct {< ValidFrom={"<=31.03.2021"}, ValidTo={">=31.03.2021"} , %Konto= P({< Konto.ValidFrom={"<=31.03.2021"}, Konto.ValidTo={">=31.03.2021"}, %Date= >} ) >} %Client)
April	Count(Distinct {< ValidFrom={"<=30.04.2021"}, ValidTo={">=30.04.2021"} , %Konto= P({< Konto.ValidFrom={"<=30.04.2021"}, Konto.ValidTo={">=30.04.2021"}, %Date= >} ) >} %Client)
...	...

A client shouldn't be count if the result of the set analysis isn't in the correct month.   

I hope we can found a solution to this problem.

Regards,
Théo

[t_donnet]

And how banks developpers resolve that? 

I am not the first one to meet this kind of demand...