topic Re: Set analysis in AGGR function in QlikView

Set analysis in AGGR function

Thu, 23 May 2013 12:01:57 GMT

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.

Re: Set analysis in AGGR function

er_mohit — Thu, 23 May 2013 12:06:25 GMT

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)))

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)))

Re: Set analysis in AGGR function

Thu, 23 May 2013 12:14:30 GMT

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.