Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi,
In you excel, output tab - can you explain how you got 4 as Unique emp count for April?
Regards,
Abey
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 ....
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!
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 ..
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!
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])))
see the attached one