topic Re: Filters and Set Analysis in QlikView

Filters and Set Analysis

Wed, 02 Jul 2014 08:08:06 GMT

I have a requirement regarding set analysis expression. In my data model under every manager no of employees work.

On my first screen I have a filter called manager and I want to count no of employees for selected manager for current year.

expression for screen1 : count({<Year={$(currYear)},trans_id={1}>}emp_id) where currYear=max(Year)

On screen 2 I want to calculate same expression but now I have only year as a filter available. Now I want to calculate this expression based on year filter although selection of manager from first screen should be applicable.

One more important point that this selected year should not affect screen1

Please suggest what is the best approach to do it ?

Tribhuwan

Re: Filters and Set Analysis

jagan — Wed, 02 Jul 2014 09:43:51 GMT

HI,

Try like this

count({<trans_id={1}>}emp_id)

If you remove Year in set analysis you always get the values for the current selections.

Regards,

Jagan.

Re: Filters and Set Analysis

Wed, 02 Jul 2014 11:19:29 GMT

Hi Jagan,

This will work for screen2. But how to manage it in screen1 as I don't want to apply year filter on screen1

Re: Filters and Set Analysis

jagan — Thu, 03 Jul 2014 01:44:03 GMT

Hi,

If you don't want to apply Year filter then use this

count({<Year=>} emp_id)

Year= will ignore year selection.

trans_id= will ignore trans_id selection.

Regards,

Jagan.

Re: Filters and Set Analysis

Anonymous — Thu, 03 Jul 2014 02:22:24 GMT

Hi,

If you don't want Year selection to be affected on screen1 than you can calculate variable in backend instead with below script.

MaxYear:

LOAD Max(Year) as Year Resident <YourTable>;

LET currYear=Peek('Year', 0, 'MaxYear');

DROP Table MaxYear;

You can use screen1 set analysis as it is. For screen2, you can just remove "Year={$(currYear)}," (see below)

Count({<trans_id={1}>} emp_id)

Thanks

Re: Filters and Set Analysis

Thu, 03 Jul 2014 05:20:14 GMT

Hi Satyadev

Thanks for response. It seems promising. One doubt I have if I will select year from screen2 for eg 2013 then screen1 will show data for 2014 or 2013 ?

In my case I want to apply year as currYear=2014 on my screen1 irrespective of year selection on screen2.

Re: Filters and Set Analysis

Anonymous — Thu, 03 Jul 2014 06:22:31 GMT

Hi Tribhuwan,

It will always show max year i.e 2014 even if you select any year in screen2 because you are calculating variable in backend at once. Calculating variable in frontend with Max() function will change the data after your every year selection.

Hope this helps you.