topic issue while calculating the difference in App Development

issue while calculating the difference

vinod2086 — Tue, 17 Jul 2018 11:00:01 GMT

Hi All,

i am facing issue while calculating the difference.

please find the below data with expected output.

ID	Distance	Created_Date	Customer	Diff_Distance
1	15	26-07-2017 06:39	Cust1	0
1	20	27-07-2017 09:09	Cust2	5
1	23	27-07-2017 09:39	Cust1	3
1	34	27-07-2017 10:39	Cust1	9
1	46	27-07-2017 13:54	Cust1	12
2	13	01-08-2017 10:24	Cust2	0
2	45	07-08-2017 07:54	Cust2	32
2	67	16-08-2017 09:54	Cust1	22
2	43	16-08-2017 10:54	Cust2	-24
3	12	16-08-2017 11:09	Cust1	0
3	10	16-08-2017 13:24	Cust3	-2
3	23	16-08-2017 13:54	Cust3	13
3	31	16-08-2017 23:39	Cust3	8

my expected output is diff_Distance.

please help on the same.

Thanks in Advance.

Re: issue while calculating the difference

vinod2086 — Tue, 17 Jul 2018 11:01:44 GMT

i am using below logic but not getting expected output.

If(RowNo() = 1, 0, Distance- peek(Distance,-1))as Diff_Distance,

Re: issue while calculating the difference

sunny_talwar — Tue, 17 Jul 2018 11:28:39 GMT

Try this

Table:

LOAD * INLINE [

ID, Distance, Created_Date, Customer

1, 15, 26-07-2017 06:39, Cust1

1, 20, 27-07-2017 09:09, Cust2

1, 23, 27-07-2017 09:39, Cust1

1, 34, 27-07-2017 10:39, Cust1

1, 46, 27-07-2017 13:54, Cust1

2, 13, 01-08-2017 10:24, Cust2

2, 45, 07-08-2017 07:54, Cust2

2, 67, 16-08-2017 09:54, Cust1

2, 43, 16-08-2017 10:54, Cust2

3, 12, 16-08-2017 11:09, Cust1

3, 10, 16-08-2017 13:24, Cust3

3, 23, 16-08-2017 13:54, Cust3

3, 31, 16-08-2017 23:39, Cust3

];

FinalTable:

LOAD *,

If(ID = Previous(ID), Distance - Peek('Distance'), 0) as Diff_Distance

Resident Table

Order By ID, Created_Date;

DROP Table Table;

Re: issue while calculating the difference

vinod2086 — Wed, 18 Jul 2018 04:09:08 GMT

Hi Sunny,

Thanks for reply.

still i am facing same issue, when i select id 2 first record should display "0" but here by default taking the above (record-current record) this is not correct. same is applicable for id 3 so on..

please let me know if you need more information.

Re: issue while calculating the difference

kaanerisen — Wed, 18 Jul 2018 07:00:38 GMT

Hi V,

Actually Sunny's solution is valid. I assume that you see different in the front-end when you add the values into a table. The reason is a sorting issue.

Default sorting (ID-Distnace-...):

Modified sorting (ID-CreatedDate-...):

Re: issue while calculating the difference

sunny_talwar — Wed, 18 Jul 2018 09:42:47 GMT

I am seeing the same output as you needed and as kaanerisen‌ showed

What is wrong here? Am I missing something? Attached the qvf for you to check....

Re: issue while calculating the difference

vinod2086 — Tue, 24 Jul 2018 08:23:04 GMT

Thanks Sunny.

its working now