Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
xavier_roty
Contributor II
Contributor II

Sort out a straight table with frequency per year and Employee

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 Employee20102011
Only 1 EC per Year2
From 2 to 12 EC per Year33
Above 12 EC per year

question QV.JPG

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

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.

Spike.PNG

Regards,

Jagan.

View solution in original post

7 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Can you attach a sample qvw file, so that it would be easier to work it out.

Regards,

jagan.

Not applicable

Hi there,

It will be easier if you will use Pivot table.

xavier_roty
Contributor II
Contributor II
Author

Hi Jagan,

Hereto is a sample qvw file.

Hope it will be easier to understand.

Regards

Xavier

jagan
Luminary Alumni
Luminary Alumni

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.

Spike.PNG

Regards,

Jagan.

xavier_roty
Contributor II
Contributor II
Author

Great Jagan,

It works perfectly.

Could you tell me how the aggr  function works? I am confused about it.

Regards

Xavier

jagan
Luminary Alumni
Luminary Alumni

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.

xavier_roty
Contributor II
Contributor II
Author

The help file is not so clear for a first approach.

Once again thanks for your help

regards

Xavier