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.

Accepted Solutions (1)

Accepted Solutions (1)

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

Answers (1)

Answers (1)

Former Member
0 Kudos

Hello Kumar,

Please create a Measure Check as

[Check]=RunningCount([Designation];([Emp No]))

and apply a block Level Filter on the Table saying [Check] equals to 1.

Regards

Niraj

Former Member
0 Kudos

Hi Niraj,

I am getting Incorrect reset context