Calculated dimension

[Anonymous]

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?

[felipedl · Accepted solution]

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.

[Anonymous]

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