Line Char Help with Start and End Date

[amit_saini]

Hi Folks,

I'm having below set of data:

Main_Order	Start	End	Cost
71501	2015-02-11	2017-12-15	8809.81
71502	2015-02-26	2017-12-01	16304.07
71503	2017-12-04	2017-12-04	20167.84
71601	2016-02-10	2017-12-01	9147.59
71603	2016-11-18	2018-05-14	16178.57

My requirement is to create chart like below from this data:

Logic : What ever is start Date like "2015-02-11" , "2015-02-26" , we need to consider this as Month number 1 and based on corresponding End Date - Month number ,need to show line trend .

So in first case for 71501 ,between 2015-02-26 and  2017-12-15 = We need to create line trend from Month 1 (Feb 2015) upto Month 35 (Dec 2017).

Like wise trend for all Main orders.

Please help , no idea how to do this.

Thanks in advance!

Regards,

AS

[sunny_talwar · Accepted solution]

You can use a script like this

Table:

LOAD *,

MonthName(Start, IterNo() - 1) as MonthYear,

IterNo() as MonthNum

While MonthName(Start, IterNo() - 1) <= MonthName(End);

LOAD * INLINE [

    Main_Order, Start, End, Cost

    71501, 2015-02-11, 2017-12-15, 8809.81

    71502, 2015-02-26, 2017-12-01, 16304.07

    71503, 2017-12-04, 2017-12-04, 20167.84

    71601, 2016-02-10, 2017-12-01, 9147.59

    71603, 2016-11-18, 2018-05-14, 16178.57

];

MonthNum will be your dimension

[olivierrobin]

hello

a question is : which amount to you want on each month ?

I would do something like that

1 load your data

Main_Order, number of months to treat (End-Start+1)

2 loop on each row

for each row

for i=1 to nbr of mont and write Main_Order,i,Amount

so that an the end you have

Main_Order,nbr,amount  (nbr varying from 1 to number of months to create)

[amit_saini]

Perfect!

Thank You!

Regards,

AS

[amit_saini]

Hi Sunny,

Can we also do if we are having just YearMonth field in data set like:

Plant	Cost	YearMonth
ABC	4893.68	201602
ABC	87329.56	201603
ABC	409588.53	201604
ABC	561346.75	201605
ABC	857989.37	201606
ABC	1311887.23	201607
ABC	1643780.32	201608
ABC	1711032.75	201609
ABC	2018146.15	201610
ABC	2237569.11	201611
ABC	2461354.99	201612
ABC	2498283.83	201701
ABC	2526209.61	201702

So on line Chart YearMonth should be shown as 201602= 1 and 201702 = 13 , in general cost from Month 1 upto 13

Regards,

AS

[sunny_talwar]

I believe so... create a new field in the script like this

LOAD Plant,

     Cost,

     YearMonth,

     AutoNumber(RowNo(), Plant) as MonthNum

FROM ....;

Make sure that the sorting is correct (first by Plant and then by YearMonth in the ascending order)

[amit_saini]

I tried , but something is wrong, so thought of sharing example:

Please see the attachment , what I'm looking form to rename YearMonth Dim as Month, automatically should start from 1 and till last based on data set.

For 71601 = from 1 upto 27

Regards,
AS

[sunny_talwar]

I tried , but something is wrong

You tried by creating a new field? What is the new field called?

[amit_saini]

I tried as per your suggestion like below :

    [Information zur Auftragsart/ zum V-Anteil] as Vpart,

    Empfängerwerk as Plant,

        AutoNumber(RowNo(), Empfängerwerk) as MonthNum

But O/P looked weird for me , so I loaded qvw without this.

Regards,

AS

[sunny_talwar]

can you add it back and show the weird