Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

vinod2086
Contributor II

issue while calculating the difference

Hi All,

i am facing issue while calculating the difference.

please find the below data with expected output.

    

IDDistanceCreated_DateCustomerDiff_Distance
11526-07-2017 06:39Cust10
12027-07-2017 09:09Cust25
12327-07-2017 09:39Cust13
13427-07-2017 10:39Cust19
14627-07-2017 13:54Cust112
21301-08-2017 10:24Cust20
24507-08-2017 07:54Cust232
26716-08-2017 09:54Cust122
24316-08-2017 10:54Cust2-24
31216-08-2017 11:09Cust10
31016-08-2017 13:24Cust3-2
32316-08-2017 13:54Cust313
33116-08-2017 23:39Cust38

my expected output is diff_Distance.

please help on the same.

Thanks in Advance.

1 Solution

Accepted Solutions
vinod2086
Contributor II

Re: issue while calculating the difference

i am using below logic but not getting expected output.

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

6 Replies
vinod2086
Contributor II

Re: issue while calculating the difference

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

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;

vinod2086
Contributor II

Re: issue while calculating the difference

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.

kaanerisen
Contributor III

Re: issue while calculating the difference

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-...):

Untitled.png

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

Untitled2.png

Re: issue while calculating the difference

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

Capture.PNG

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

vinod2086
Contributor II

Re: issue while calculating the difference

Thanks Sunny.

its working now

Community Browser