Two Dates In A Table Set Analysis Cartesian Product

[tdudley0302]

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!

[sunny_talwar · Accepted solution]

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

[marcus_sommer]

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

[PrashantSangle]

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,