topic Re: [Hard] Set analysis with multiple dates by month in App Development

[Hard] Set analysis with multiple dates by month

t_donnet — Fri, 16 Jul 2021 07:50:20 GMT

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:

A between in the same field
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

Re: [Hard] Set analysis with multiple dates by month

marcus_sommer — Fri, 16 Jul 2021 09:47:52 GMT

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

Re: [Hard] Set analysis with multiple dates by month

t_donnet — Fri, 16 Jul 2021 11:53:00 GMT

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

Re: [Hard] Set analysis with multiple dates by month

marcus_sommer — Fri, 16 Jul 2021 15:06:10 GMT

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

Re: [Hard] Set analysis with multiple dates by month

t_donnet — Mon, 19 Jul 2021 06:20:30 GMT

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

Re: [Hard] Set analysis with multiple dates by month

marcus_sommer — Mon, 19 Jul 2021 07:23:19 GMT

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

Re: [Hard] Set analysis with multiple dates by month

t_donnet — Tue, 10 Aug 2021 11:32:02 GMT

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

Re: [Hard] Set analysis with multiple dates by month

marcus_sommer — Tue, 10 Aug 2021 13:35:07 GMT

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

Re: [Hard] Set analysis with multiple dates by month

t_donnet — Wed, 11 Aug 2021 14:10:48 GMT

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

Re: [Hard] Set analysis with multiple dates by month

t_donnet — Wed, 11 Aug 2021 14:13:03 GMT

And how banks developpers resolve that?

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

Re: [Hard] Set analysis with multiple dates by month

marcus_sommer — Wed, 11 Aug 2021 15:17:35 GMT

Your datamodel may be correct but I'm not sure if it's specially suitable for your data and requirements.

Recommended - especially by larger datasets - is to develop a datamodel in the direction of a star-scheme. Means to have a single fact-table and n dimension-tables. And this in regard to the development efforts, logically easiness, maintainability as well as the UI performance. Link-table approaches are usually rather the worst case.

In regard to my suggestion to apply one or multiple intervalmatch logigs I must be a bit more preciser. I really meant the logic behind an intervalmatch but not to apply them within the classical and inbuilt way - which creates rather large link-tables with additionally synthetic keys. Personally I don't use it in this way else I apply appropriate while loops, like:

t: load date(from + iterno() - 1) as DATE from source while from + iterno() - 1 <= to;

which is IMO much more flexible and enables me to associate the data without link-tables.

Transferred to your case I would try to resolve all from + to fields to real dates - at least for all matching/linking tasks and avoiding the link-tables. Like above mentioned the final logics within the UI should be as simple as possible so that simple user-selections are enough to control the used subset of data. To have all the from + to and loading + displaying them within the UI doesn't mean that they must be used to make all matchings with them else it would be better to flag, (pre-)calculate and associate the data already within the datamodel.

Beside this your intended set analysis logic within the table isn't possible in this way. The reason for it is that the measure is identically for all rows - the variable has always one value and won't change within the row to apply :

ValidFrom={"<=31.01.2021"}, ValidTo={">=31.01.2021"} for January and
ValidFrom={"<=28.02.2021"}, ValidTo={">=28.02.2021"} for February and so on ...

To evaluate something in this way you would need a logic like:

if(Month = 'January', exprJanuary, if(Month = 'February', exprFebruary ...

whereby such an approach couldn't be recommended - neither form the efforts to develop the logic nor from the performance point of view.

Therefore I must repeat my suggestion to review the whole datamodel and to develop a real Qlik datamodel instead like it looked of transferring a sql-scheme with more or less adjustments to Qlik.

- Marcus