Load script doubt

[#1]

Hello everyone,

I have data like shown below:

mop	order
3/1/2012	10
4/1/2012	35
5/1/2012	63
6/1/2012	80
7/1/2012	120
8/1/2012	200
9/1/2012	390
10/1/2012	638
11/1/2012	1250
12/1/2012	2599
1/1/2013	3200
2/1/2013	4320
3/1/2013	7980

But i want change like below in my load script:

mop	withinmonth	1month	2month	3month	4month	5month	6month	>6 and <=12month
3/1/2012	10	35	63	80	120	200	390	19987
4/1/2012	35	63	80	120	200	390	638	19349
5/1/2012	63	80	120	200	390	638	1250	18099
6/1/2012	80	120	200	390	638	1250	2599	15500
7/1/2012	120	200	390	638	1250	2599	3200	12300
8/1/2012	200	390	638	1250	2599	3200	4320	7980
9/1/2012	390	638	1250	2599	3200	4320	7980	0
10/1/2012	638	1250	2599	3200	4320	7980	0	0
11/1/2012	1250	2599	3200	4320	7980	0	0	0
12/1/2012	2599	3200	4320	7980	0	0	0	0
1/1/2013	3200	4320	7980	0	0	0	0	0
2/1/2013	4320	7980	0	0	0	0	0	0
3/1/2013	7980	0	0	0	0	0	0	0

Could you please advise me

Best,

Robert

[SergeyMak]

Hi Robert,

What is your date format? Is it M/D/YYYY?

How do you calculate the last column?

And the last question: Why do you need this? What is the goal?

[#3]

i think crosstable(mop,order)

[#4]

Hi,

Yes, My date fromat is M/D/YYYY and all date would be month start only.

Best,

Robert

[#5]

Hi,

Please find attached the sample Qvw file.

Its very urgent for me so i would be really grateful if anyone give appropriate solution for my question.

Best,

Robert

[sujeetsingh]

What i will recommend you is to use peek and previous these two functions are inter record functions that can be used in your scenario.

[#7]

Hi,

I am not aware the functions that you suggested me.

Please help me to do it in my sample application.

Best,

Robert

[Eric_Tay]

Hi Robert,

Perhaps you can consider using set analysis for each of the column. For example: the expression for 'within month' column can be:

count(

{$

<Month(mop) = Month(Date('1/1/2013','MM/DD/YYYY')>

}

order).

[sujeetsingh]

Follow this thread

Peek() vs Previous() – When to Use Each

[#10]

Hi,

I want do it in my data model since i want to do relation another table to make another calculation.

I would expected dynamic calculation. 

Best,

Robert