Set Analysis and date ranges

[alexis]

I have a very unusual problem/requirement:

Assume I have 2 tables

CALENDAR

TRANSACTIONS

CALENDAR table that has all the usual fields like Year, Quarter, Month, Day, DateNo, TranDate

linked to a

TRANSACTIONS table that has columns like TranDate, TranType, Amount, TranFlag, DateIN, DateOUT, TransRecordNo (this one is always 1) etc....

The user gets to select a date (or date range) and any number of other parameters from a Multi-select box (e.g TranType)

The application needs to calculate 2 things:

a) Total Amount for selections which is Sum(Amount)

b) Total number of records that have TranFlag=3 for the period chosen and where the DateIN and DateOUT is in range of the selected date period.

= Sum ({1<[TranFlag] = {'3'}, [DateIN] = {<=$(=Max(DateNo))'}, [DateOUT] = {>=$(=Min(DateNo))'} >}  TransRecordNo)

If you start off and select a month (say April 2014) then Min(DateNo) is 41365 (1st April 2013) and Max(DateNo) is 41394 (30th April) and the formula above works perfectly.

If, however the user now selects TranType of 9 (say) from the multi-select and there are transactions of this type only for dates 5th, 7th and 11th April then:

Min(DateNo) is 41369 (5th April 2013) and Max(DateNo) is 41375 (11th April), so my formula above gives incorrect results because I am interested in the Number of records for the date range chosen (1st April until 30th) not the period that the "TranType" selection has resulted in.

Is there a way that I can resolve this.

Thanks in advance

Alexis

[Gysbert_Wassenaar]

Try putting set modifiers in the max and min functions too:

= Sum ({1<[TranFlag] = {'3'}, [DateIN] = {<=$(=Max({1<Month=p(Month)>}DateNo))'}, [DateOUT] = {>=$(=Min({1<Month=p(Month)>}DateNo))'} >}  TransRecordNo)

[#3]

A possibility is to create a secondary calendar not linked to the transaction table (a data island) and make your date selections in this table. Then refer to the data island in your set analysis.

[swuehl · Accepted solution]

Use a set expression to disregard all unwanted selections from your Max(DateNo) / Min(DateNo) like

= Sum ({1<[TranFlag] = {'3'}, [DateIN] = {<=$(=Max({<TranType= >}DateNo))'}, [DateOUT] = {>=$(=Min({<TranType= >}DateNo))'} >}  TransRecordNo)

to disregard selections in TranType

[alexis]

Hi Gysbert - thanks for replying

Your solution will not work as your will search at the ENTIRE set which is not what I am looking for in this instance.

Alexis

[alexis]

Interesting approach swuehl.

The above is a simplification of my app. In place of "TranType" I have another 6-7 fields.

I will give "Helpful" or "Answer" in a bit once I have had a chance to try it...

Thanx again

[alexis]

Thanks once again Swuehl.

What is the syntax if there were multiple fields that we were ignoring not just TranType

Thanks in advance

Alexis

[#8]

= Sum ({1<[TranFlag] = {'3'}, [DateIN] = {<=$(=Max({<TranType=, AnotherField=, YetAnotherField= >}DateNo))'}, [DateOUT] = {>=$(=Min({<TranType=, AnotherField=, YetAnotherField= >}DateNo))'} >}  TransRecordNo)