Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

xavier_roty
New 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
MVP
MVP

Sort out a straight table with frequency per year and Employee

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.

7 Replies
MVP
MVP

Sort out a straight table with frequency per year and Employee

Hi,

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

Regards,

jagan.

Not applicable

Re: Sort out a straight table with frequency per year and Employee

Hi there,

It will be easier if you will use Pivot table.

xavier_roty
New Contributor II

Re: Sort out a straight table with frequency per year and Employee

Hi Jagan,

Hereto is a sample qvw file.

Hope it will be easier to understand.

Regards

Xavier

MVP
MVP

Sort out a straight table with frequency per year and Employee

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
New Contributor II

Re: Sort out a straight table with frequency per year and Employee

Great Jagan,

It works perfectly.

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

Regards

Xavier

MVP
MVP

Re: Sort out a straight table with frequency per year and Employee

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
New Contributor II

Re: Sort out a straight table with frequency per year and Employee

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

Once again thanks for your help

regards

Xavier

Community Browser