Set Analysis Expression....

[tmumaw]

I am trying to use set analysis to sum a dollar value if the document create date is between two other dates, however it says there is nothing wrong with the syntax it's not working.  Does anyone see anything wrong with my statement?  All dates have been converted to numeric fields.

sum({<Contract_Create_Date_Num = {">=[Camp_Start_Date_Num]<=[Camp_end_Date_Num]"}>} [Subtotal 1])

Thanks

[#2]

Hi, you must peek only one value, not all of them.

So you need to use max, min, only, etc.

Use like this:

sum({$<Contract_Create_Date_Num = {">=$(=max([Camp_Start_Date_Num]))<=$(=max([Camp_end_Date_Num]))"}>} [Subtotal 1])

[Oleg_Troyansky · Accepted solution]

Thom,

I can only guess that the reason is that there are multiple values available for your start and end dates, and therefore the expression cannot be rendered globally. To overcome that, I'd try to look for a level of detail that can guarantee that the two fields are unique. For example, if those fields are unique per Contract, then I'd reformat your condition like this:

sum({<Contract_ID = {"Contract_Create_Date_Num>=[Camp_Start_Date_Num] and Contract_Create_Date_Num<=[Camp_end_Date_Num]"}>} [Subtotal 1])

if the two fields are still not unique per contract, you may have to use min() or max() to arrive to the single value.

The other way of solving it would be calculating a flag in the script (assuming that the three date fields are easily accessible) and assigning 1 to the contracts that fit your condition. Then, the set analysis becomes very simple:

sum({<Contract_Date_Flag = {1}>} [Subtotal 1])

cheers,

Oleg Troyansky

www.masterssummit.com

[MayilVahanan]

Hi

Try like this

sum({$<Contract_Create_Date_Num = {">=$(=max([Camp_Start_Date_Num]))<=$(=max([Camp_end_Date_Num]))"}>} [Subtotal 1])

[tmumaw]

Hi Oleg,

I have a group of contracts which were created on different days. I have a group of campaigns which has a start dates and an end dates. I need to see if the contract create date falls within the valid start and end dates for the
campaign. When I use this ({<Contract_Create_Date_Num = {">=41449 <=41479"}> } [Subtotal 1]) (as a test it works fine).  When I try and add the logic you and the rest of the forum provided it doesn’t work.  I’m confused…..

Thanks
Thom

[tmumaw]

Oleg,

Thanks for your help.  After reviewing the model, a few left joins here and a resident table here I was able to use the simple method.  Made my life much easier.

Thanks

Thom

sum({<Contract_Date_Flag = {1}>} [Subtotal 1])

P.S. Thanks to everyone else to for the suggestions.