topic Set_expression blocks regular user_selection in spite of $? in QlikView

Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 10:00:33 GMT

Hi,

I have a chart with the date as dimension and a COUNT(DISTINCT) as expression.

=> The tricky thing is, I need to display two values and a quota out of these two

- One of those values is available every day (so it is there for today)

- The other is available only up to yesterday

=> That would give me a quota of 100% for "today" which is false.

=> To avoid that, I have to block the display for "today"

=> To that end, I have a set_expression in my formula that looks something like this

>>> {$<%Date < TODAY>} <<<

<=> That set_expression works fine - but, what I didn't realize until now just because I never tested it - is that it seems to block regular user_selections on that same date_field >> %Date << - when I select only one day on that field, the expression where I have that set_expression does not react, only the other one where I don't have it - which is again wrong 😉

Can somebody help me here?

Thanks a lot!

Best regards,

DataNibbler

Re: Set_expression blocks regular user_selection in spite of $?

tresB — Mon, 16 Dec 2013 10:04:09 GMT

May be you can try like:

%Date={"<$(=TODAY())"}

Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 10:17:34 GMT

Hi Tresesco,

no, unfortunately that doesn't work.

I had that set_expression slightly bigger, but yours works just the same - in this case, though, the result is the same:

- The formula works and the value for "today" is not displayed.

<=> When I select CW 50 and then I select just Dec13 (Fri), still the whole week is displayed.

Re: Set_expression blocks regular user_selection in spite of $?

tresB — Mon, 16 Dec 2013 10:20:21 GMT

Could please post a sample with expected output described?

Re: Set_expression blocks regular user_selection in spite of $?

swuehl — Mon, 16 Dec 2013 10:28:24 GMT

{$<%Date < TODAY>}

is not a valid set expression. You can't use a comparison operator like < in a field modifier.

Besides this, what is TODAY? A field? A variable?

Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 10:58:39 GMT

Hi,

that set_expression in my first post was just an example. As I said, the real one does work insofar as the value for "today" (a variable) is not displayed.

@ tresesco

I am trying to put together a sample - for some reason, now I get a value for every day of this week - there should be no values for the rest of the week, though...

Re: Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 11:29:26 GMT

Hi,

I think now I've made it. I'll attach

- an Excel file with some fake personell_data

- a qvw to display the presence_quota

As you see, sickness_data is not available for "today", only presence_data is available -> so I get a presence_quota of 100% for "today" which would be wrong

=>´To avoid that, I introduced that set_expression to display the values only up to "yesterday"

<=> That now seems to block any regular user_selections on that date_field.

Re: Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 12:27:04 GMT

Hi,

seems I've made it.

Reading up on "set operators", I realized what I should be using: The "-=" operator.

I ended up with this formula (irritatingly, it is nearly all underlined in the editor, but it says "Expression ok", and it seems to be working:

>>> COUNT({$<%Datum -= {'16.12.2013'}>} DISTINCT Pers_Nr5) <<<

I only now have to dynamize today's date. But the effect is what I wished for: The user can select any one day from the week before - but the value is not displayed for today.

Best regards,

DataNibbler

Re: Re: Set_expression blocks regular user_selection in spite of $?

swuehl — Mon, 16 Dec 2013 12:40:00 GMT

Have you already tried using a dollar sign expansion similar to what tresesco suggested above?

COUNT({$<%Datum -= {'$(=today())'}>} DISTINCT Pers_Nr5)

Re: Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Mon, 16 Dec 2013 13:21:34 GMT

Hi swuehl,

yes, I have.

It seems to be working now.

Thank you all!

Best regards,

DataNibbler

Re: Set_expression blocks regular user_selection in spite of $?

datanibbler — Tue, 17 Dec 2013 08:22:07 GMT

Hi all,

it works now all right - I just find it very confusing that the expression_editor underlines nearly the entire expression - and says "Expression OK" at the same time 😉

I think that has already been entered as an "idea" at least once. Do you think that the editor is ever going to be up to all the functions QlikView can perform?