topic Re: Line Char Help with Start and End Date in QlikView

Line Char Help with Start and End Date

amit_saini — Fri, 18 May 2018 14:52:51 GMT

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,

Re: Line Char Help with Start and End Date

sunny_talwar — Fri, 18 May 2018 15:01:25 GMT

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

Re: Line Char Help with Start and End Date

olivierrobin — Fri, 18 May 2018 15:05:12 GMT

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)

Re: Line Char Help with Start and End Date

amit_saini — Mon, 21 May 2018 10:56:05 GMT

Perfect!

Thank You!

Regards,

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 12:26:31 GMT

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,

Re: Line Char Help with Start and End Date

sunny_talwar — Tue, 22 May 2018 12:29:42 GMT

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)

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 12:57:18 GMT

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

Re: Line Char Help with Start and End Date

sunny_talwar — Tue, 22 May 2018 13:03:00 GMT

I tried , but something is wrong

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

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 13:07:21 GMT

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,

Re: Line Char Help with Start and End Date

sunny_talwar — Tue, 22 May 2018 14:42:15 GMT

can you add it back and show the weird

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 15:14:28 GMT

One Min Sir

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 15:32:37 GMT

PFA

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 15:36:33 GMT

This one too.

Re: Line Char Help with Start and End Date

sunny_talwar — Tue, 22 May 2018 16:13:15 GMT

This table that you are adding the AutoNumber to, doesn't have any Month_Year information....Do you have a table where you have Plant and MonthYear in a single table?

Re: Line Char Help with Start and End Date

amit_saini — Tue, 22 May 2018 17:22:55 GMT

Sorry , no.

Month , Year and Cost is coming from my fact table. For cost analysis via Plants , I have used another table carrying Plant info . I'm doing Left Join by Key called Main_Orders on Fact table/

Regards,

Re: Line Char Help with Start and End Date

sunny_talwar — Tue, 22 May 2018 21:42:04 GMT

May be take a resident load of the Fact Table and create the MonthNum field in the resident load like this

AutoNumber(Year_Month, Plant)

I have made the updates in the attached qvw file for you to check and run the updates

Re: Line Char Help with Start and End Date

amit_saini — Wed, 23 May 2018 07:52:10 GMT

Thank u so much