Discussion Board for collaboration related to Creating Analytics for QlikView.
Hello to all,
This is my first post and i thank you for any reply.
I am updating a QV base about expenses claims (EC) with 30 files each month.
In each file I have for each employee a various number of expenses claims with numerous type of expenses.
I would like to sort out a straight table with :
employee doing only 1 EC per year
employee doing from 2 to 12 EC per year
employee doing more than 12 EC per year.
If i do a table with dimension = 'IDEmployee' and expression aggr(…) I will find an answer but this is not at all satisfactory for me.
I guess i should add an extracolumn during load ?
If you have any idear ?
Thank's again for any reply
For example, with the screen shot below, i would like to have the following results :
Go to Solution.
Convert Straight table to Pivot table
Add year as Dimension.
Add the following following 3 expressions
Only 1 EC Per Year =Sum(AGGR(If(Count(DISTINCT idEC) = 1, 1, 0), IDEmployee, Year))
From 2 to 12 EC Per Year =Sum(AGGR(If(Count(DISTINCT idEC) >= 2 AND Count(DISTINCT idEC) <= 12 , 1, 0), IDEmployee, Year))
Above 12 EC Per Year - =Sum(AGGR(If(Count(DISTINCT idEC) > 12, 1, 0), IDEmployee, Year))
Finally pivot the expressions and years.
Can you attach a sample qvw file, so that it would be easier to work it out.
It will be easier if you will use Pivot table.
Hereto is a sample qvw file.
Hope it will be easier to understand.
It works perfectly.
Could you tell me how the aggr function works? I am confused about it.
Aggr function calculates the Sum/Count by Fields regardless of Dimension. Check the help file or online for further information.
Ex: AGGR(If(Count(DISTINCT idEC) = 1, 1, 0), IDEmployee, Year)
The above expression calculates the count by IDEmployee and Year.
It is similar to group by in SQL.
Group by IDEmployee, year;
The help file is not so clear for a first approach.
Once again thanks for your help