topic Re: Re: set analysis script help in QlikView

set analysis script help

Wed, 18 Dec 2013 10:14:19 GMT

Hi All,

i have a attached a excel file . First tab contains data while second tab has result set .

Please give me the expression which would do the trick . note - Result needed by using frontend only , no backend modifications permitted .

Thanks,

Vikram

Re: set analysis script help

Wed, 18 Dec 2013 10:48:36 GMT

Here I need to use condition => Overall Training Man Days of that Employee should be greater than or equal to 2. So for April , i would get empid's 5,7,9,10 hence 4

For may , i would get 2,4 ,6 hence 3 +april's 4 = 7

For june , i would get 3,9 but we already got 9 in april so only 1 + april's 4 + may's 3 = 8

.Hope my question is more understandable now

Re: set analysis script help

abeyphilip — Wed, 18 Dec 2013 12:44:23 GMT

Hi,

In you excel, output tab - can you explain how you got 4 as Unique emp count for April?

Regards,

Abey

Re: set analysis script help

Wed, 18 Dec 2013 13:07:29 GMT

Ok Thanks Abey for showing interest .Let me explain this again in detail .

1) For april we have 4 employees whose total [Training Man Days] for the month of april is greater than or equal to 2 .

those employees are 5 ,7 , 9 ,10 . hence for April result is 4

2)For May we have 3 employees whose total[Training Man Days]for the month of may is greater than or equal to 2,

those employees are 2,4,6. hence for may we should get( distnct [employees of april + employees of May] )= 5,7,9,10,2,4,6 hence for may result is 7

3( For June there are 2 employees whose total[Training Man Days]for the month of may is greater than or equal to 2,

those are 3,9 , but 9 is already presnt in April , hence for june we should get (distinct[employees of april + employees of May + employees of June]), hence for june ,result should be 8.

similarly for other months ....

Re: Re: set analysis script help

jerem1234 — Wed, 18 Dec 2013 17:26:44 GMT

See if attached is what you are looking for. I loaded in your data, then used a straight table with dimension of Month and expression:

count({<[Training Man Days] = {'>=2'}>}if(Month = aggr(min({<[Training Man Days] = {'>=2'}>}Month), [Emp ID]), [Emp ID]))

Then I checked full accumulation on the expression tab.

Hope this helps!

Re: set analysis script help

Thu, 19 Dec 2013 13:22:36 GMT

jerem1234

Thats useful but issue comes when there is a person whose training man days for a month is 0.50,0.50,1, this person's id should be included ,in your example such person's id wont be included.

so inshort we need something like count({<sum([Training Man Days]) = {'>=2'}>}if(Month = aggr(min({<sum([Training Man Days]) = {'>=2'}>}Month), [Emp ID]), [Emp ID])).

waiting for your reply ..

Re: Re: set analysis script help

jerem1234 — Thu, 19 Dec 2013 18:10:22 GMT

Please find attached. To incorporate it with a sum makes it a bit more tricky. The formula I ended up with is:

count(distinct if(aggr(sum({1}[Training Man Days]), [Emp ID], Month) >=2, if(Month = aggr(min({1}if(aggr(sum({1}[Training Man Days]), [Emp ID], Month) >=2, Month)), [Emp ID]), [Emp ID])))

Since they are sums, you can't use the set analysis to reduce the sets. So I had to add it a couple of aggregations, and convert some set analysis to if statements. I also added in two records of EMP ID 11 to August to test out the summing. That's why the last number is 10 and not 9.

Hope this helps!

Re: Re: Re: set analysis script help

preminqlik — Sun, 22 Dec 2013 05:16:40 GMT

hey jerem nice job.... you could add some more to your application like this :

count({<Year={'$(=max(Year))'},Month1=,Day=,Month={'<=$(=max(Month))'}>}distinct if(aggr(sum({<Year={'$(=max(Year))'},Month1=,Day=,Month={'<=$(=max(Month))'}>}[Training Man Days]), [Emp ID], Month) >=2, if(Month = aggr(min({<Year={'$(=max(Year))'},Month1=,Day=,Month={'<=$(=max(Month))'}>}if(aggr(sum({<Year={'$(=max(Year))'},Month1=,Day=,Month={'<=$(=max(Month))'}>}[Training Man Days]), [Emp ID], Month) >=2, Month)), [Emp ID]), [Emp ID])))

Re: set analysis script help

Kushal_Chawda — Tue, 24 Dec 2013 09:22:36 GMT

see the attached one