topic Re: Set analysis - plz help in QlikView

Set analysis - plz help

datanibbler — Tue, 23 Jul 2013 15:56:50 GMT

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

Re: Set analysis - plz help

Anonymous — Tue, 23 Jul 2013 16:04:01 GMT

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) "}>}

Re: Set analysis - plz help

datanibbler — Tue, 23 Jul 2013 16:13:14 GMT

OMG - I'll have to write this down.

Thanks a lot, Michael!

Best regards,

DataNibbler

Re: Set analysis - plz help

datanibbler — Wed, 24 Jul 2013 08:14:14 GMT

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

Re: Set analysis - plz help

Anonymous — Wed, 24 Jul 2013 10:20:48 GMT

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

Re: Set analysis - plz help

datanibbler — Wed, 24 Jul 2013 10:28:44 GMT

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

Re: Set analysis - plz help

Anonymous — Wed, 24 Jul 2013 10:31:03 GMT

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

Re: Set analysis - plz help

datanibbler — Wed, 24 Jul 2013 10:39:33 GMT

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.

Re: Set analysis - plz help

Anonymous — Wed, 24 Jul 2013 12:33:31 GMT

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)

Re: Set analysis - plz help

datanibbler — Wed, 24 Jul 2013 13:28:08 GMT

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.

Re: Set analysis - plz help

Anonymous — Wed, 24 Jul 2013 16:45:26 GMT

I got a few free minutes... From your explanation, it should be this:

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

As for the example - you can reduce the application to make it small, and scramble the data (except these two dates).

Re: Set analysis - plz help

datanibbler — Thu, 25 Jul 2013 08:20:06 GMT

Hi Michael,

we're still now quite on the same page:

As I told you - making it stranger still - it works fine for the current period (the syntax example you have would return the number of employees that joined before this month's start and did not leave until today. That works fine.

What does not work is when I try the same, only replacing (Monthstart(TODAY())) with (Monthstart(TODAY(), -1))

It is then that I suddenly get different values.

I will see about the example asap - but it might be a while. Sorry, I have to jump a bit between my priorities and something else is more pressing as it is wished_for by management - I'll be back here as soon as I've had the time to do some more testing. Please follow this thread.

Best regards,

DataNibbler