Set analysis with multiple GetFieldSelections

[#1]

Hi,

Im struggling with a set analysis where I'm using GetFieldSelections to to only show information of the selections.

What I have i two different tables with their own dates. When the user chose a date from the first table the second table should only show information from the same date. Im not using a calendar because the data model wont allow that without giving me a loop.

Table 1 - Agreements

• AgreementID
• SalesID
• AgreementYear
• AgreementMonth

Table 2 - Transactions

• TransactionID
• SalesID
• TransactionYear
• TransactionMonth

Table 3 - Sales

• SalesID
• SalesName

The user wants to know during a AgreementYear and AgreementMonth, how many transactions was it the same period. The user should be able to chose any Year and Month and there is no restriction of how many from each column.

The set analysis I'm trying to use is below:

Count({$<

                              TransactionYear = {$(=SubField(GetFieldSelections(AgreementYear,'|'),'|'))},

                              TransactionMonth = {$(=SubField(GetFieldSelections(AgreementMonth,'|'),'|'))}

                    >} DISTINCT TransactionID)

The problem I get with this calculation is that its only taking the first Year and the first Month from each list - not all the different selections.

Is there another way to write this calculation?

Thanks in advance!