Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
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.