topic Re: Use variables in set analysis in QlikView

Use variables in set analysis

t_hylander — Thu, 28 Jul 2016 09:16:35 GMT

Hi,

Ive got a couple of charts that uses the same selections in their set analysis and I thought Id use a variable instead so I can change them all from one place.

ex.

count({<customer={'1001','1002','1003'}, type -= {'10','20'} >} orders)

What Id like is to replace the whole line and in some cases add more selection so it might look something like this;

Count({<$(vCustomerTypeSelection), artnr={'2423','4222'} >} orders)

Is it possible, or do I have to settle with two variables contaning just the "numbers" (in the above case)?

Thanks in advance!

/Tomas

Re: Use variables in set analysis

sunny_talwar — Thu, 28 Jul 2016 10:49:02 GMT

I think this should be possible:

Count({<$(vCustomerTypeSelection), artnr={'2423','4222'} >} orders)

The only issue would be the syntax editor won't understand the syntax, but that should not stop you from getting the same result as this

Count({<customer={'1001','1002','1003'}, artnr={'2423','4222'}>} orders)

Re: Use variables in set analysis

trdandamudi — Thu, 28 Jul 2016 10:54:02 GMT

It is possible and I did used in various applications..

Count({<$(vCustomerTypeSelection), artnr={'2423','4222'} >} orders)

Re: Use variables in set analysis

t_hylander — Thu, 28 Jul 2016 11:00:49 GMT

I havnt used varables much, how would the set/let look like for it to work?

Re: Use variables in set analysis

t_hylander — Fri, 29 Jul 2016 09:28:23 GMT

Ive now also having trouble using dates...

In load-script I have this;

Let vCurrYear = Year(now());
Let vCurrMonth = month(now());
Let vCurrWeek = week(now());
Let vCurrDay = date(now());

and in a simple text-object I tested with this; (and a couple of other variants)

count({1<%Date={'${=$(vCurrDay)}'}>}Transnr)

This just shows a '0' (where I know it should display '11')

Year, month and week works as they should, like this;

count({1<År={$(vCurrYear)}>}Transnr)

%Date is a key to a calendar and its content is like '2016-07-29' (the same as 'date(now())' is)

Ive also tried;

count({1<%Date={=date(now())} >}Transnr)

but it generates the error "Error in the set modifier ad hoc element list: ',' or ')' expected"

Any clues?

Re: Use variables in set analysis

sunny_talwar — Fri, 29 Jul 2016 09:43:44 GMT

Would you be able to share a sample where you might be testing all this out?

Re: Use variables in set analysis

jonathandienst — Fri, 29 Jul 2016 09:46:54 GMT

>>I havnt used varables much, how would the set/let look like for it to work?

Set vCustomerTypeSelection = customer={'1001','1002','1003'};

Re: Use variables in set analysis

rubenmarin — Fri, 29 Jul 2016 09:49:09 GMT

Hi Tomas, about the dates...

count({1<%Date={'${=$(vCurrDay)}'}>}Transnr) --> try:

count({1<%Date={'$(vCurrDay)'}>}Transnr)

count({1<%Date={=date(now())} >}Transnr) --> try:

count({1<%Date={'$(=date(now()))'}>}Transnr)

Re: Use variables in set analysis

t_hylander — Fri, 29 Jul 2016 09:58:10 GMT

Tried but didnt work...

Re: Use variables in set analysis

sunny_talwar — Fri, 29 Jul 2016 11:00:55 GMT

Seems to be working:

Re: Use variables in set analysis

rubenmarin — Fri, 29 Jul 2016 11:01:44 GMT

Hi Tomas, your date field is called transdate, set analysis is asigned to a field.

Also the number field is transnr (all lowercase), try with:

=count({1<transdate={'$(vCurrDay)'}>}transnr) & chr(13) &

count({1<transdate={'$(=date(now()))'}>}transnr)

Edit: As Sunny posted above, if you only want the date, is better to use Today() instead of Now().

Re: Use variables in set analysis

t_hylander — Fri, 29 Jul 2016 11:52:08 GMT

sorry, I was inconsistent with names and sloppy when I made the exemple...in the exemple it works yes.

But...

Thanks to you I got this to work;

count({1<%Date={'$(=date(now()))'}>}Transnr)

but this doesnt;

Count({1<%Date = {"$(=Date(Today()))"}>} Transnr)

If I use

Let vCurrDay = date(now());

and then

count({1<%Date={'$(vCurrDay)'}>} Transnr)

it all works now but not with "let vCurrDay = today()" or "date(today())"

Re: Use variables in set analysis

rubenmarin — Fri, 29 Jul 2016 12:44:45 GMT

Hi Tomas, Now() and Today() can have a parameter wich tells the date to return based on:

0 - Script execution

1 - Function call

2 - Document opened

Default for Today() is '2', default for Now() is '1', the thing with option '1' is that system clock changes so often that it can cause an impact on performance, but this is when expression are related between them, usually it works fine.

You can also test in a text box if there is a difference, create a text box with =Date(Now()) and another with =Date(Today()).

I get the same result with both so I don't know why Today() isn't working for you, maybe a typo? Anyway, if Now() gives a good performance keep them.

Re: Use variables in set analysis

t_hylander — Mon, 01 Aug 2016 09:22:27 GMT

Its a small app so performance isnt a issue. I'll go with now() for now

Thanks!