Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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?
i think crosstable(mop,order)
Hi,
Yes, My date fromat is M/D/YYYY and all date would be month start only.
Best,
Robert
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
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.
Hi,
I am not aware the functions that you suggested me.
Please help me to do it in my sample application.
Best,
Robert
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).
Follow this thread
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