6 Replies Latest reply: Feb 4, 2010 4:22 PM by Philippe Motillon

# Set Analysis Issue.

Here is the expression I have trouble with:

num(if(sum(BFINTRDEG)= 0 ,null(),SUM({\$< BFINTRN_DT_FROM={"<=\$(=DateFrom)"}, BFINTRN_DT_TO={">=\$(=DateFrom)"} >} BFINTRDEG)),'#,##0.00')

If the DateFrom Field as only one value ( Selected) , everything works fine.

If I select more than one value, that does not work ( the sum shows 0).

Any idea.

Philippe

• ###### Set Analysis Issue.

John Witherspoon once replied to one of my questions:

>>>>

[...]

I don't think that will work if you have no or more than one Region selected. I think this will, though (untested):

sum({1<Region={'\$(=concat(Region,chr(39)&','&chr(39)))'} Sales)

[...]

<<<<

Basically it means to concat all selected values and compare with that. I don't know if this will work with >=.

Best,
Thilo

• ###### Set Analysis Issue.

Hello Philippe,

Why don't store the date you want to compare with in a variable (easier to set in set analysis) and help the user input the date say with a slider/calendar object?

• ###### Set Analysis Issue.

Hi Plilippe,

I think that the formula is working correctly. The reason being that since more than one date is availble for DateFrom, the formula =DateFrom cannot be evaluated (it has to have a single value to compare. This is my understanding.

Regards,

Nimish

• ###### Set Analysis Issue.

Philippe,

the syntax \$(=DateFrom) would only return value if a single value is available. For multiple values, you should use min(DateFrom). However, the function min() returns a number and not a date. Set Analysis is very sensitive to the Date/time format. The format of your date field has to be PRECISELY the same as the date format of the other date field. So, to make it work, you need to use something like this:

\$(=date(min(DateFrom), 'MM/DD/YYYY')),

assuming that this is the date format that you use for your other date field.

cheers,

• ###### Set Analysis Issue.

THanks a lot Oleg,

That works just well....

Philippe

• ###### Set Analysis Issue.

THanks a lot Oleg,

That works just well....

Philippe