Help required for calculation in chart for date difference

[#1]

Dear Expert,

Need help for EQUNR_S to get Act_Typ difference for same field Doc-Date.

How to calculate the same in chart or while loading.

ID	Ship_Key	Doc_Date	EQUNR_S	Act_Typ	get date difference
1	0000000001000101397Z20	01.02.2011	1397	ER	0
13	0000000002000101397Z30	01.02.2011	1397	E	0.00
10076	0000001797000201397Z30	10.06.2011	1397	F	129.00
10078	0000001798000201397Z30	13.06.2011	1397	D	3.00
10080	0000001799000201397Z20	13.06.2011	1397	T	0.00
11256	0000002262000101397Z20	16.07.2011	1397	ER	33.00
13189	0000003284000101397Z30	29.10.2011	1397	I	105.00

Thanks

Ashif

[Gysbert_Wassenaar]

Maybe like this:

LOAD

     ID,

     Ship_Key,

     Doc_Date,

     EQUNR_S,

     Act_Typ,

     If(EQUNR_S=previous(EQUNR_S),Doc_Date - previous(Doc_Date),0) as [get date difference]

FROM

     ...somewhere...

;

[#3]

Dear Gysbert,

Above data is sor

Its giving 0 value in load statement as mentioned by you.

Thanks

Ashif

[Gysbert_Wassenaar]

Your Doc_Date field contains only one value. Every record has the same Doc_Date value, so there's never a difference between the Doc_Date value of one record and the Doc_Date value of another record.

[#5]

I've loaded all the records with example. Check the same. There is difference in date.

[Gysbert_Wassenaar]

I think the problem is that your data is not sorted by EQUNR_S first. You can resolve that using a resident load with an order by clause.

Temp:

LOAD * FROM YourQVD (qvd);

Result:

LOAD *, if(previous(EQUNR_S=EQUNR_S, Doc_Date - previous(Doc_Date),0) as TT;

Resident Temp

Order By EQUNR_S, Doc_Date;    

[jagan]

Hi,

Can you post the entire script, are you using any aggregate functions like Sum(), Count(), Max(), Min() etc.  If you are using then use Group by in script like below

Load EQUNR_S,

     Doc_Date,

Sum(SomeField) AS Total

FROM DataSource

Group by EQUNR_S, Doc_Date; -- You have to specify all the fields here given in the Load.

Hope this helps you.

Regards,

Jagan.

[#8]

Now i want the chart to appear like this

ID	EQUNR	Act_Typ	Sender	Receiver	Doc_Type	Doc_Date	Operation time
2015	3	ER		0000002948		01/02/2011
2169	3	E	0000002948	0000002948		01/02/2011	282
14511	3	F	0000002948	0000002948		10/11/2011	296
25160	3	D	0000002948	0000002948		01/09/2012	30
25667	3	ER	0000003398	0000003398		01/10/2012	0
25668	3	C	0000003398	0000003398		01/10/2012	0
25668	3	C	0000003398	0000003398		01/10/2012	0
25666	3	T	0000002948	0000003398		01/10/2012

Help me with chart expression to get the result as current result is getting like this.

EQUNR	Act_Typ	Operation time
3	D	-578
3	F	326

[jagan]

Hi,

Add one more column in script like below

Result:

LOAD *, if(previous(EQUNR_S=EQUNR_S, Doc_Date - previous(Doc_Date),0) as TT,

if(previous(EQUNR_S<>EQUNR_S, 1, 0) AS Flag,

if(previous(EQUNR_S=EQUNR_S, RangeSum(Peek([TT]), Doc_Date - previous(Doc_Date)), (Doc_Date - previous(Doc_Date)) AS AccumSum

Resident Temp

Order By EQUNR_S, Doc_Date;  

Now in front end

Chart : Straight Table

Dimension: EQUNR, Act_Typ

Expression: Sum({<Flag ={1}>}AccumSum)

Hope this helps you.

Regards,

Jagan.

[#10]

Still not gettong the desired result.

ID	EQUNR_S	Act_Typ	Doc_Date
2015	3	ER	01/02/2011	0
2169	3	E	01/02/2011	282
14511	3	F	10/11/2011	296
25160	3	D	01/09/2012	30
25666	3	T	01/10/2012	0
25667	3	ER	01/10/2012	0
25668	3	C	01/10/2012	0