Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Xavier
For example, with the screen shot below, i would like to have the following results :
Number of EC per Employee | 2010 | 2011 |
---|---|---|
Only 1 EC per Year | 2 | |
From 2 to 12 EC per Year | 3 | 3 |
Above 12 EC per year |
Hi,
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.
Regards,
Jagan.
Hi,
Can you attach a sample qvw file, so that it would be easier to work it out.
Regards,
jagan.
Hi there,
It will be easier if you will use Pivot table.
Hi Jagan,
Hereto is a sample qvw file.
Hope it will be easier to understand.
Regards
Xavier
Hi,
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.
Regards,
Jagan.
Great Jagan,
It works perfectly.
Could you tell me how the aggr function works? I am confused about it.
Regards
Xavier
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.
SELECT
Count(Distinct idEC)
FROM Table1
Group by IDEmployee, year;
Regards,
Jagan.
The help file is not so clear for a first approach.
Once again thanks for your help
regards
Xavier