topic Re: Apparent set analysis inconsistency in App Development

Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 08:41:43 GMT

Hello

In my Datamodel there is a calendar, linked to the fact table, as usual…:

Now, the measure in the table below has a set analysis which selects the dates above April 20^th

The field “DataMovSettimana” contains the week, it is calculated in the script as Week(DataMov).

As you can see, the current selection is DataMovSettimana=18.

Then, as expected, the result is the intersection of the set analysis and the current selections, thus showing only dates pertaining to week 18.

So, the result of the table below is the expected one.

Then, I remove the dimension “DataMovSettimana” from the table, without modifying anything in the selections, and the result is shown below:

As you can see, the measure total value is different as it apparently disregards the selection of the week. Moreover, the espression “week(DataMov)” is not calculated for the dates excluded in the set analysis.

Then, I remove one of the selections (not the week). Now the result is correct….

Moreover, how this can be explained?

In order to debug it, I made a copy of the date fields, both in the fact table and in the calendar table

This is the result. Does it make sense? Anyway, it is a workaround for me in order to get correct results.

I guess there is some datamodel issue but I am running out of ideas to throubleshoot it.

Any suggestion?

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 09:32:42 GMT

Check the format of DataMov in m-movimie table.
Seems that the relation key is not equal and you're calculating 22-28/04 dates based on the values in m-movimie table which are not related to calendar values.
When you select smth in Calendar, it works fine as those rows which are not related are disregarded.

Also as a thought, you might need to enclose your date value into single quotes in this case >='20/04/2024'

Re: Apparent set analysis inconsistency

marcus_sommer — Tue, 21 May 2024 10:43:02 GMT

There is no reason to apply a formatting to DataMov without specifying a different format as the default one. Therefore I assume that the date isn't a date else a timestamp which remains a timestamp regardless how it's formatted - even by date(DataMov, 'YYYY') it would be a timestamp and not a year.

If my above assumption is right, you will need something like this:

date(floor(DataMov))

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 11:00:39 GMT

I first thought the same thing, already tried date(floor()), same issue....

Re: Apparent set analysis inconsistency

Lech_Miszkiewicz — Tue, 21 May 2024 11:11:00 GMT

I will say your calendar does not link to all records in your fact table. For test you can create duplicate of DataMov field in fact table DataMov as DataMovFact and DataMov as DataMovCal and then put all 3 fields DataMov, DataMovFact and DataMovCal in single table as diemension. If you see any gaps that is your answer.

Cheers

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 11:14:46 GMT

Not sure if it's a timestamp issue, he's made copies of fields and both copies are not working:

In case of a timestamp issue at least the last sum() with DataMov_movime={} would work as expected

Re: Apparent set analysis inconsistency

marcus_sommer — Tue, 21 May 2024 11:18:35 GMT

Make sure that's identically applied in the facts as well as within the calendar.

A chart with expressions isn't very helpful to check the exists and data-quality and also the associations between them. Better will be to use a table-box and set there all relevant fields side by side - here the date-key + the fact/dimension-date + the week + the other object-dimension and the measure-field. I assume something will look odd and could be investigated in more depths.

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 11:57:37 GMT

No, the two copies of the date fields are working. The '91.654' value that you see is correct. The result is not the correct one if I use the key date field.

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 11:58:48 GMT

Already done. Nothing odd, everything appears correct with no misalignments, no NULLs, no holes...

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 14:19:59 GMT

Ok, Let's start from the beginning. The issue below is because you're trying to get Week(Date) in measure without aggregation. Measures return Null() when the result is a range instead of value. You can try Only(Week(Date)).

As for the second part about set analysis, I have one theory what is the issue and this not obvious.
Can you do a favour and get a table with 2 dimensions: DateMov and Num(DateMov)? I would imagine you'll have multiple values of Num(DateMov) for each DateMov

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 14:28:52 GMT

Unfortunately, this is not the case...

and no duplicates in the numeric dates....

guess the issue is something more tricky

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 14:49:04 GMT

Paolo... these 2 are Dimension and measure
Please Do 2 Dimessions: DataMov and Num(DataMov)
Or in your implementation do Count(Distinct Num(DataMov)) instead of Num(DataMov).
I'm pretty sure I get what's happening there

Re: Apparent set analysis inconsistency

marcus_sommer — Tue, 21 May 2024 14:50:15 GMT

Were there really all relevant fields included in the table-box? Just the dates are not enough else all DataMov versions and all fields from the object dimensions + measures as well as all selected fields. Often helpful is also to include an unique key and if none exists to create one with recno() and/or rowno() - just to ensure to see all data and to have a view on all possible duplicates.

Beside this make sure that there are no synthetic keys or circular loops within the data-model.

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 14:56:33 GMT

Yes, you are right, but nevertheless, there should be something else

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 15:00:58 GMT

I think they've done a crazy thing and combined the As-of table with an actual Calendar using dual values In their DateKey. Which explains everything including the case when Only(Week(Date)) is not working

Re: Apparent set analysis inconsistency

paolo_cordini — Tue, 21 May 2024 15:07:31 GMT

It is not this case.
I am not the author of the script but it is quite straightforward. It's a simple usual calendar, no As-of table...

Re: Apparent set analysis inconsistency

Firefly_cam — Tue, 21 May 2024 15:25:20 GMT

That's the only way I can explain this and it makes sense. Also it explains why you need two identical dates in TabCalendario.

This case appears to be almost untraceable, tried on my end and only this formula worked:
Date(Num(Date))=Date#(Text(Date))

Re: Apparent set analysis inconsistency

marcus_sommer — Tue, 21 May 2024 15:29:06 GMT

I think the calendar is not the problem else the DataMov from the facts - and an extra parallele num() + isnum() and similar might be useful there. Further if there are several fact-tables with DataMov as key repeat the doubling of them as separate fields and apply them too in the above suggested table-box. It's very like that your issue comes from not proper associations.

Re: Apparent set analysis inconsistency

paolo_cordini — Thu, 23 May 2024 11:41:57 GMT

I simplified a lot the datamodel for debugging purpose.I kept only the fields involved in the example.

From here, if I use a LEFT KEEP when loading the "m-prosta" table then the issue is solved (the "ProdNome" field is included in the selections. But I have no explanation for this behavior.

Re: Apparent set analysis inconsistency

marcus_sommer — Thu, 23 May 2024 12:13:54 GMT

I think it means that the associations between the data were not suitable for your view and the selection within a sub-dimension restricted the other selections and/or the reverse was happens and there were a n:m relationship which wasn't covered by the object granularity/expressions.

Without diving deeper it's hard to say what's the issue but the above mentioned table-box with all related fields and depending on the data a recno() + rowno() from each single table should have provide hints in regard to gaps and/or a poor data-quality and/or any duplicates.

Many of such association-trouble could be avoided by developing the data-model from the beginning as a star-scheme which means a single fact-table with n surrounding dimension-tables.