Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set analysis in AGGR function

Hi I am trying to calculate the movement of employees from one dept to another dept across different time taken as an input from the user.

i am using the following expression

-(count(if(aggr(count(DISTINCT {<[Run Date]={'$(vBase)'},EmployeeType={'Employee'},Month=,Year=>} Active_ID)
-
count(DISTINCT {<[Run Date]={'$(vCompareMonth)'},EmployeeType={'Employee'},Month=,Year=>} Active_ID),Active_ID,[DEPT_LEVEL1])=1,Active_ID)))
-count(DISTINCT if([Run Date]=vBaseMonth and EmployeeType='Employee' and max_emp_date<vCompareMonth ,Active_ID)))

THE AGGR FUNCTION RETURS ME CORRECT VALUE WHEN I DONT MAKE ANY SELECTION IN MY DOCUMENT BUT ONCE I AM MAKING A YEAR SELECTION MY NUMBERS ARE GETTING REDUCED.

HOW DO I REMOVE THE FILTER SELECTION EFFECT FROM MY AGGR FUNCTION.


2 Replies
er_mohit
Master II
Master II

Try this

-(count(if(aggr(count(DISTINCT {<[Run Date]={'$(vBase)'},EmployeeType={'Employee'}>}Active_ID)
-
count(DISTINCT {<[Run Date]={'$(vCompareMonth)'},EmployeeType={'Employee'}>}Active_ID),Active_ID,[DEPT_LEVEL1])=1,Active_ID)))

or

this

-count(DISTINCT {<[Run Date]={'$(vBase)'},EmployeeType={'Employee'}>}Active_ID)
-
count(DISTINCT {<[Run Date]={'$(vCompareMonth)'},EmployeeType={'Employee'}>}Active_ID),Active_ID,[DEPT_LEVEL1])=1,Active_ID)))

Not applicable
Author

Mohit thanks for the suggestion.I tried this also but it is returning zero since i have month and year selected in my time filter. Do you have any idea on how can we remove the filter effect in the AGGR table. my vBase and vCompareMonth are mapped to my time filter but if i remove all the filters and hardcode the dates its returning the correct value.