2 Replies Latest reply: Dec 20, 2017 3:21 AM by Jaroslav Stanek

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?

• 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:

Load * Inline

[

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:

load

User,

min(Month) as minMonth

Resident dummy

group by User;

left join (dummy)

Load

User,

minMonth as Month,

1 as [Num]

Resident Max;

NoConcatenate

data:

Load

User,

Month,

[Number of sales],

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

Load

*

Resident dummy

order by User;

drop table dummy, Max;

The following code gets me both of your tables above:

Attached the QVW I used.

• Re: Calculated dimension

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