Set analysis: multiple selections in one field

[#1]

Hi all,

I would like to count something if the archief_month is smaller than 1 field and equal to the reporting date field.

Any ideas how to do this with set analysis? (could not find it in the help)

This does not work I think:

count({< archief_month = {"<=archief_verwacht_month", reporting_date} >}  something )

Thank you very much

[swuehl · Accepted solution]

Hi Martijn,

I would assume that above expression won't work anyway in a set expression because you want to compare on record base if achief_month e.g. equals reporting_date, do you?

If so, I would try

=count(if (achief_month <= achief_verwacht_month and achief_month = reporting_date, something))

If you have difficulties with performance, you may put a flag in your script created by above expression and then do

=count({<flag = {1} >} something)

Hope this helps,

Stefan

[swuehl]

Hi Martijn,

just changed my mind, try something like

=count({< archief_month = {"=archief_month <=archief_verwacht_month and archief_month=reporting_date"} >}  something )

(BTW, I assume it is possible to compare a reporting DATE with a archief_MONTH, right?)

Regards,

Stefan

[#4]

Hi Stefan,

You are correct, archief_month <=archief_verwacht_month are 2 fields in a table which should be analyzed per row of this table, because they both belong to 1 fact. So I agree with your first reply. (but then, not off course with your second right?)

reporting date is a global variable, independent of tables.

yes all dates are in the correct format, a month is the first day of the month like 1-1-2011

I will try also your second one, but in theory is was a wrong one because of the row by row comparison?

thanks a lot,

regards Martijn

[swuehl]

Hi Martijn,

I think the second one could also work using an advanced search for the element_set (as is), though I am not sure if the performance will be much better than using the if() function. Just give it a try...

Have a nice day,

Stefan