Set analysis - plz help

[datanibbler]

Hi,

can anyone explain to me how I can use set analysis combining (operators other than 😃 with a dynamic expression?

- With an expression, I know that $(=) thing. Sometimes, for some reason I also have to enclose that in quotes.

- With other operators, the examples in my book say I have to use double quotes ("").

=> How to combine the two so I will have, e.g.:

  >>  Date1 after the start of the month before now <<

Thanks a lot!

Best regards,

DataNibbler

[Anonymous · Accepted solution]

When I want to use date conditions, aspecialy more than one, I use this set:

{<DateField={"$(= '>=' & date(expression for one date) ) & '<=' & date(expression for another date) "}>}

[datanibbler]

OMG  - I'll have to write this down.

Thanks a lot, Michael!

Best regards,

DataNibbler

[datanibbler]

Hi Michael,

sorry, can you help me once more? I just cannot spot the mistake:

I have this code:

count({1<Geschlecht = {"0"}, Eintritt={"$(= '<=' & Monthstart(TODAY(), -1))"}, Austritt = {"$(= '>=' & Monthend(TODAY(), -1))"}>}DISTINCT PN)

That should just return the count of employees of this gender in the last month. It does return a value, but different from the one I get using just the following:

count({$<Geschlecht = {0}>}DISTINCT PN)

and manually selecting the dates, using advanced search expressions - exactly the same expression, I've checked several times.

Thanks a lot!

Best regards,

DataNibbler

[Anonymous]

Hi Friedrich,

It is essential to use date() function in set, like

date(Monthstart(TODAY(), -1)))

Same for the monthend, or any date expression at all.  It is an important part of the generic example in my previous answer.

And, no need for "" around 0.

Regards,

Michael

[datanibbler]

Hi,

I understand that "0" is over the top, it was just a try. Why the DATE()? The functions

- TODAY() and

- Monthstart(TODAY(), -1)

return a date, don't they?

Thanks a lot!

Best regards,

DataNibbler

[Anonymous]

not really, it is in fact a timestamp...

[datanibbler]

Hi,

it is still returning different values: I have now changed it to

    count({1<Geschlecht = {0}, Eintritt={"$(= '<=' & DATE(Monthstart(TODAY(), -1)))"}, Austritt = {"$(= '>=' & DATE(Monthend(TODAY(), -1)))"}>}DISTINCT PN)

Still, when I compare it to that other code that works only with regular selections, it is different.

It is also possible that the reason lies elsewhere - I have the date originally in format YYYYMMDD, but as a STRING - so I rearrange it in the script to have it in DD.MM.YYYY format. The table viewer shows me it's now a date.

The strange thing about this is that when I use the same thing to specify the current month, it works fine and it returns the same result that I get without any set_analysis (safe for the gender) when just doing regular selections, whether or not I use that additional DATE() function.

[Anonymous]

What you have now is technically correct.  The question is - is it correct logically? 
First, there is nothing to return if you look for the date earlier than month start and later than the same month end.
Next, the function monthend() returns the last milisecond on the last date, which is in your case the end of the day June 30, 2013.  Date() returns the integer date, that is the beginning of July 30.  The counts for that date may be not included, depending on formats.
In your expression, try this:

count({1<Geschlecht = {0}, Eintritt={"$(= '>=' & DATE(Monthstart(TODAY(), -1)))"}, Austritt = {"$(= '<' & DATE(Monthstart(TODAY(), 0)))"}>}DISTINCT PN)

If it doesn't work, upload an example.  (Not sure if I'll have more time today, sorry)

[datanibbler]

Hi Michael,

thanks for the help!

I'm afraid I won't be able to provide an example. I'm handling personell data here, and I have a bad feeling about it myself since this really is a red-hot issue in Germany...

Eintritt and Austritt are two different dates - "Eintritt" is the date an employee joined the company, "Austritt" is when he left it.

I want to capture, sticking with this example, all employees that joined the company BEFORE the start of this month and who did not yet leave - that is, their leaving date is after today.

It is also logically correct as far as I can oversee it - but it doesn't work. I'm puzzled.