topic Set Analysis Query in QlikView

Set Analysis Query

Thu, 02 Jul 2009 16:42:09 GMT

Hi Team,

I have list of Employees for two quarters - As on Mar2008 and As on Jun2009

I am comparing Jun2009 with Mar2008.
In a bar chart, i made two expressions

1. Count

({1<[As On Date]={'31/03/2008'}>} Distinct EmpId) - to get count for Mar2008

2. Count({$} Distinct EmpId) - to get count for Jun2009 (I have made Jun2009 as Already Selected)

Till now it is OK. But problem occurs when i am making selections in other criteria like Region, etc. In that case, i am getting Region wise values for Jun2009 but not for Mar2008.

Is there any way in Set Analysis so that i can get filtered values for Mar2008 also.

Thanks.

Arun Mittal

Set Analysis Query

Thu, 02 Jul 2009 17:16:07 GMT

can you post an example?

Roberta

Set Analysis Query

Thu, 02 Jul 2009 17:37:46 GMT

see in the attached file..if i do select in Region, As On 31/03/2008 is not changing as it is based on the set analysis formula as i told earlier. I want that these values should also get changed when i do selection in Region (i.e. As On 31/03/2008 for the selected region)

Set Analysis Query

Thu, 02 Jul 2009 18:50:08 GMT

Try with $ (current selection) instead of 1 :

Count

({$<[As On Date]={'31/03/2008'}>} Distinct EmpId

)

Roberta

Set Analysis Query

Thu, 02 Jul 2009 19:18:36 GMT

Thanks a lot Roberta. Its working now.

Bit more help.. Can u pls elaborate it to me.

As i thought {1<[As On Date]={'31/03/2008'}>} like take full set ie 1 and in that take the records where date is 31/03/2008. I took full set because 26/06/2009 is already selected. Can u pls explain me while $ denotes current selection (here 26/06/2009) how this is working.

Set Analysis Query

Thu, 02 Jul 2009 20:56:20 GMT

Excuse me: I only speek a little english..

I try to explain you : the expression <[As On Date]={'31/03/2008'}> is a "set modifier" : when it is written immediately after the set identifier (1= all, $= current selction) it is interpreted as a modification of corresponding set.

So ,independently from [As On Date] field's selected value, if you use set identifier = $ the expression will be calculated on all data corresponding to [As On Date]='31/03/2008' and all other selection's restriction, otherwise, if you use set identifier = 1 , the expression will be calculated on all data corresponding to [As On Date]='31/03/2008' disregarding selection.

i hope i've been able to give you a valid answer.

Roberta

Set Analysis Query

johnw — Fri, 03 Jul 2009 01:12:53 GMT

One thing to be careful of when using $ and a date field is that you usually have a calendar connected to the date field, and you should usually list ALL of the connected calendar fields in the expression.

To explain, let's say you have fields Date, Month and Year. The user might select from any or all of these fields. They might, for instance, select 26/06/2009 for Date, Jun-09 for Month and 2009 for Year. If you then just do this:

count({$<Date={'31/03/2008'}>} distinct EmpId)

It won't work, because although you've modified the Date field, Qlikview is still (because of the $) using the selected values for Month and Year. So you have to list them all. In this case, telling it to ignore the selections in Month and year.

count({$<Date={'31/03/2008'},Month=,Year=>} distinct EmpId)

Also, $ is the default, so it doesn't have to be specified explicitly:

count({<Date={'31/03/2008'},Month=,Year=>} distinct EmpId)