topic Set Analysis Issue. in QlikView

Set Analysis Issue.

Thu, 04 Feb 2010 16:42:25 GMT

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.

thanks for your help

Philippe

Set Analysis Issue.

Thu, 04 Feb 2010 17:00:05 GMT

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.

Miguel_Angel_Baeyens — Thu, 04 Feb 2010 17:51:42 GMT

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.

Thu, 04 Feb 2010 18:11:00 GMT

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.

Oleg_Troyansky — Thu, 04 Feb 2010 19:35:04 GMT

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.

Thu, 04 Feb 2010 21:22:37 GMT

THanks a lot Oleg,

That works just well....

Philippe

Set Analysis Issue.

Thu, 04 Feb 2010 21:22:44 GMT

THanks a lot Oleg,

That works just well....

Philippe