# QlikView Extensions

Discussion Board for collaboration on QlikView Extensions.

New Contributor

## Calculated dimension

Hello,

Im dealing with current problem, hopefully someone will have some idea I got input data like this:

 Month User Number of sales IX A 12 X A 15 XI A 19 XII A 11 XI B 7 XII B 14 XII C 10

The usual table output would be:

 IX X XI XII A 12 15 19 11 B 7 14 C 10

But I would need:

 1 2 3 4 A 12 15 19 11 B 7 14 C 10

Basicly I need to compare employees based on number of months they are with company. I think calculated dimension is way to go as far as the input table is not the simple as the above one :-) Its made from more tables & their connections... Any hints?

1 Solution

Accepted Solutions
Valued Contributor III

## Re: Calculated dimension

Hi Jaroslav,

You'll have to do some script transformations to get what you need, by getting the minimum month for each user (I also changed the roman literal month number into a integer number):

dummy:

[

Month,User,Number of sales

9,A,12

10,A,15

11,A,19

12,A,11

11,B,7

12,B,14

12,C,10

];

Max:

User,

min(Month) as minMonth

Resident dummy

group by User;

left join (dummy)

User,

minMonth as Month,

1 as [Num]

Resident Max;

NoConcatenate

data:

User,

Month,

[Number of sales],

if (User = peek(User), peek(Num)+1,1) as Num;

*

Resident dummy

order by User;

drop table dummy, Max;

The following code gets me both of your tables above:

Attached the QVW I used.

2 Replies
Valued Contributor III

## Re: Calculated dimension

Hi Jaroslav,

You'll have to do some script transformations to get what you need, by getting the minimum month for each user (I also changed the roman literal month number into a integer number):

dummy:

[

Month,User,Number of sales

9,A,12

10,A,15

11,A,19

12,A,11

11,B,7

12,B,14

12,C,10

];

Max:

User,

min(Month) as minMonth

Resident dummy

group by User;

left join (dummy)

User,

minMonth as Month,

1 as [Num]

Resident Max;

NoConcatenate

data:

User,

Month,

[Number of sales],

if (User = peek(User), peek(Num)+1,1) as Num;

*

Resident dummy

order by User;

drop table dummy, Max;

The following code gets me both of your tables above:

Attached the QVW I used.

New Contributor

## Re: Calculated dimension

Thank you Felip! I think I can make it working...