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]

Please try.
[data]:
LOAD mop,
      order
FROM
[http://community.qlik.com/thread/141358]
(html, codepage is 1252, embedded labels, table is @1)

;


NoConcatenate
data1:
LOAD *,
IF(isNULL(Previous(Month5)),0,Previous(Month5)) AS Month6;
LOAD *,
IF(isNULL(Previous(Month4)),0,Previous(Month4)) AS Month5;
LOAD *,
IF(isNULL(Previous(Month3)),0,Previous(Month3)) AS Month4;
LOAD *,
IF(isNULL(Previous(Month2)),0,Previous(Month2)) AS Month3;
LOAD *,
IF(isNULL(Previous(Month1)),0,Previous(Month1)) AS Month2;
LOAD mop,
order AS withinmonth,
IF(isNULL(Previous(order)),0,Previous(order)) AS Month1;
LOAD *
Resident data
Order by mop desc;

DROP Table data;
 

But You didn't answer how to calculate the last column... It's not clear at the moment.

[#3]

Hi,

Many Thanks. this is what i expected, the last column sum of(Month7 to Month 12).

Best,

Robert

[SergeyMak]

It's still unclear. I tried to sum up the numbers and couldn't get the numbers you have.

Could you give examples? If you need it, sure.

Regards,

Sergey

[#5]

mop	withinmonth	Month1	Month2	Month3	Month4	Month5	Month6
3/1/2012	10	35	63	80	120	200	390
4/1/2012	35	63	80	120	200	390	638

In order to get >6 and <=12month for  3/1/2012(Mop):

10/1/2012	638	Month7
11/1/2012	1250	Month8
12/1/2012	2599	Month9
1/1/2013	3200	Month10
2/1/2013	4320	Month11
3/1/2013	7980	Month12
19987	>6 and <=12month

In order to get >6 and <=12month for  4/1/2012(Mop):

11/1/2012	1250	Month7
12/1/2012	2599	Month8
1/1/2013	3200	Month9
2/1/2013	4320	Month10
3/1/2013	7980	Month11
19349	>6 and <=12month

Did you get what i mean ?

Best,

Robert

[SergeyMak · Accepted solution]

Hi,

updated script bellow

[data]:
LOAD mop,
      order
FROM
[http://community.qlik.com/thread/141358]
(html, codepage is 1252, embedded labels, table is @1)

;


NoConcatenate
data1:
LOAD*,
IF(isNull(Peek('sum')),0,Previous(Month6)+Peek('sum')) AS [sum];
LOAD *,
IF(isNULL(Previous(Month5)),0,Previous(Month5)) AS Month6;
LOAD *,
IF(isNULL(Previous(Month4)),0,Previous(Month4)) AS Month5;
LOAD *,
IF(isNULL(Previous(Month3)),0,Previous(Month3)) AS Month4;
LOAD *,
IF(isNULL(Previous(Month2)),0,Previous(Month2)) AS Month3;
LOAD *,
IF(isNULL(Previous(Month1)),0,Previous(Month1)) AS Month2;
LOAD mop,
order AS withinmonth,
IF(isNULL(Previous(order)),0,Previous(order)) AS Month1;
LOAD *
Resident data
Order by mop desc;

DROP Table data;

Regards,

Sergey

[#7]

Hi,

Let me clearly explain my logic for last column:

For each mop, I want to calculate (withinmonth, Month 1 ... Month 6)  but for last column that is what i mentioned >6 and <=12month That is suppose to be Month12.  The metrics calculation would be cumulative sum of( Month7,Month8,Month9,Month10,Month11,Month12).

In my data model, i no need to calculate Month7,Month8,Month9,Month10,Month11,Month12  but i want sum cumulative of (Month 7 to Month 12).

for 3/1/2012 -mop below is the value for month 7 to Month 12. if you sum all the i can get the Month 12 .. 

10/1/2012	638	Month7
11/1/2012	1250	Month8
12/1/2012	2599	Month9
1/1/2013	3200	Month10
2/1/2013	4320	Month11
3/1/2013	7980	Month12

Answer would be for last column of 3/1/2012  - 19987

My sincere apologizes for my previous explanation was not clear for you.

Best,

Robert

[SergeyMak]

I have exactly same result as you need

PFA

[#9]

Many Thanks

[#10]

Hi Sergey Makushinsky,

Many Thanks for your solution. its really helps me for one of my vital report.

Best,

Robert

[SergeyMak]

Hi Robert,

No worries.

I just have to add that it's better for you to follow this thread for the future

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

As suggested by sujeetsingh

Regards,

Sergey