topic Re: Two Dates In A Table Set Analysis Cartesian Product in App Development

Two Dates In A Table Set Analysis Cartesian Product

tdudley0302 — Wed, 13 Jun 2018 20:52:55 GMT

Hello,

I have the following data model that can't currently be changed:

What I would like to do is sum up a field in the UnderwritingClaimsData table whenever the %UnderwritingDate is Equal to the selected Earnings Date. Here is my set analysis:

SUM({<[%UnderwritingDate] = [Earnings Date] >} [xxx])

This works fine when one Earnings Date is selected. But when multiple Earnings Dates Are Selected and the data is displayed in the table I get a cartesian product where the numbers make no sense.

Is there a way in set analysis to make the table ignore all the rows where Earnings Date != %UnderwritingDate? I can do it by creating an If Statement but it is incredibly slow and was wondering if there is a magic way in Set Analysis to accomplish the same thing.

Thanks in Advance!

Re: Two Dates In A Table Set Analysis Cartesian Product

sunny_talwar — Thu, 14 Jun 2018 01:51:44 GMT

From what I see and understand... I don't think that this is possible using set analysis.

Re: Two Dates In A Table Set Analysis Cartesian Product

marcus_sommer — Thu, 14 Jun 2018 06:49:34 GMT

In general you could refer the selections from one field within the set analysis, for example with p() and/or e(), like:

SUM({<[%UnderwritingDate] = p([Earnings Date]) >} [xxx])

But I doubt that this will work in your case because you mentioned cartesian results within your table which hints that the associations between these tables/fields aren't suitable for this kind of query. Just to have a connection between tables doesn't mean that you could combine each fields from them in a table and get the right results. I assume you need to rethink your datamodel ... probably in the direction of merging tables or some table-parts instead of linking them through bridge-tables.

- Marcus

Re: Two Dates In A Table Set Analysis Cartesian Product

PrashantSangle — Thu, 14 Jun 2018 07:11:41 GMT

just a thought why don't you create flag in back end.

Try to combine all table and convert data model to one table then create flag field and use that field in set analysis.

Regards,