cancel
Showing results for 
Search instead for 
Did you mean: 

Both Function in WEBI

Former Member
0 Kudos

Dear Experts,

We have one requirement example below.

Emp No    Name          Designation   Start Date     End Date

1              XXX             ADMIN   1/1/2015         1/10/2015

1              XXX             HR          1/5/2015         1/9/2015

2              YYY             HR          1/1/2015         1/10/2015


Now we need to display employees with a single designation. Output should be like i need to display


Emp No    Name          Designation   Start Date     End Date

2              YYY             HR          1/1/2015         1/10/2015


and I need to exclude the other 2 rows because i need to display employees who worked only on one designation and exclude if he had worked on 2 designations.  This has to be done from report level as we don't have access to Universe. This is urgent requirement.


Thanks in Advance.

View Entire Topic
amitrathi239
Active Contributor
0 Kudos

create Variable=Count([Emp No];All) In ([Name])

Apply the filter on this and select where count is equal to 1.

Former Member
0 Kudos

Hi Amit,

This will not work in our scenario, let me expand my question.

Emp No    Unique Number         Designation   Start Date     End Date     Work Start Date   Work End Date

1                   XXX                         ADMIN        1/1/2015         1/10/2015          1/12/2015          12/10/2015

1              XXX                  HR          1/5/2015         1/9/2015       1/10/2015          12/10/2015  

2              YYY                  HR          1/1/2015         1/10/2015      1/12/2015          12/10/2015      

3              YYY                  HR          1/1/2015         1/10/2015      1/11/2015          12/11/2015 


Now i need output to show only


2              YYY                  HR          1/1/2015         1/10/2015      1/12/2015          12/10/2015      

3              YYY                  HR          1/1/2015         1/10/2015      1/11/2015          12/11/2015


first 2 rows should get excluded because the person(XXX) worked  on both Admin and HR, and event occurs on different dates in some cases. and i need to show only the person YYY as he worked on HR in both line items. Report should display only HR related designations for the same person n number of times also

amitrathi239
Active Contributor
0 Kudos

try this.

=Count([Emp No];All) In ([Emp No]) and filter the value where Not equal to 2.

Former Member
0 Kudos

Hi Amit,

Thank you for the solution provided, it worked out.

I have another question related to same report. I need to create a 2nd tab with the same emp no (from first tab) which is followed by other columns like depart name  and some measures.

I tried to apply the same formula from first tab but i still getting all the values. Any suggestions please

Regards

amitrathi239
Active Contributor
0 Kudos

Drag the formula in the table and see what are the values coming there.or share your sample data screenshot.

Former Member
0 Kudos

The red circle is wrong this was excluded in tab 1 whereas i am getting values in 2nd tab. 2418 value is the count variable created which was applied on 1st tab.

The blue circle is values are correct as this got same department.

I would like to explain u in detail. I have created 2 queries.

1st Query has EMP ID 9568608 and  in second query i have taken a query filter EMP ID Inlist result from 1st query.

So in my second tab i need to show only the emp id which are available in in 1st tab.

Regards

amitrathi239
Active Contributor
0 Kudos

have you applied the same filter in Tab2 which is applied in the tab1?

Former Member
0 Kudos

Yes i have applied. And other thing is when i removed emp id filter in query and executed for a period the first scenario didn't work now. i am getting both emp id's. this is in tab1