calculation between row

Voon Loong Hew

Summary

I need to calculate the difference of two rows

Content

In ODV, I need to calculate the difference of two value in different row of the table. I can't find appropriate function in ODV calculation. would appreciate if anyone could advise or show example of how to do that. Thanks.

For instant, I want to calculate the difference of row3-row2 (222-89).  how to do that in ODV calculation ?

Value

128

89

222

16

Philippe Lions-Oracle

Voon, sorry my message may not have been clear, the syntax I gave was symbolic (not to be text-copied in DV). Here is a screenshot of a calculation that computes the difference between row and previous row in a report. Let me know if not what you needed.

Thanks

Philippe

 

Philippe Lions-Oracle

Hi Voon, in the viz that you send in your screenshot, can you test simply removing the date column and confirm if the calculation is still not working ?

let me know pls

Philippe

Philippe Lions-Oracle

Hello Voon, if your need is to calculate the difference of metric M between row n and row n-1, you can use msum function this way :

the value of M.row n-1 is equal to : msum(M, 2) - M.row n

hence calculating the difference M.row n - M.row n-1 comes down to entering this formula in your cell : M.row n - (msum(M, 2) - M.row n)

Let me know if not what you needed

Philippe

Voon Loong Hew

Hello Philippe, 

Thanks for the answer. However, I tried it doesn't work.

I just want to create a new calculation in DV.  The M.row n is an invalid expression in the Calculation. 

Perhaps we are not talking about the same condition. I am creating data visualization and want to create new Calculation to find the difference between rows.

I can do it with Msum(M,2)-Msum(M,1).

I am having problem to find the days difference between two date in different row too. I use "Timestampdiff" function, but I can't point to different row.

any idea how to do that ?

 

Thanks,

Voon

 

Philippe Lions-Oracle

notice that in the example above, the sorting of the data is critical to the calculation.

Also, your question about difference between two dates (any dates, not necesseraly two subsequent rows) requires a different syntax. Can you give an example of what you are trying to do ?

Philippe

Voon Loong Hew

Thank you very much for the answer. I still found some problem....see attached screen shot

I use   Cost-(Msum(Cost,2)-Cost). however, the second item is wrong. It should be 200, not 300.  any idea what's wrong of my calculation ?

 

Also, as you see in the picture, I can only calculate the timediff between two column. But, I want to calculate the day difference between Received date of product B and completed Date of Product A. I can not figure out how to point to different row of the date.  Please kindly advise if you know how to do that.

 

 

 

 

 

Voon Loong Hew

hi Philippe,

Yes, it work after removing the date. So, could you please help me in detail how the Msum work ? how does it link to the date ?

Thanks a lot.

- Voon

Philippe Lions-Oracle

Hello Voon, thanks for having confirmed the testing. I believe the msum gives a precedence to sorting on date. Hence, as the sorting is on date first, the msum values do not sequence properly by item in your example.

So,

- the simplest solution, if acceptable, would be to have two vizs : the one where the MSUM is would not have the date column.

- Otherwise, to keep all objects in the same viz, you can still reasonably configure the MSUM to do the proper group by. This video shows you how to do this : https://youtu.be/LkRHUlXvTbQ.

let me know if this does not answer your question.

Thanks

Philippe

Voon Loong Hew

Thank you very much for help, Philippe.

how about the date difference between two rows in my example above ? I just want to calculate how many days between two date of difference row. In the above example, I want to know how long between the Received date of product B and Completed Date of Product A. That mean cell D2-cell E1.

any hint would be very helpful. 

Thanks again.

-Voon

 

Isabela Radivoi

Hi Philippe,

I hope you are well.

I came across this post and I think this is what I need to work out the difference in amount for each month.

I have tried to work it out myself based on the formula above and apply it to my report and although this seems to be working to some extent, unfortunately, I might need to tweak this but no idea how.

I am looking to get the amount difference of gross earnings between each 'payroll period month' at the employee level.

For example - as shown in the screenshot attached, the first employee on the list has April, May and Jun payroll run. I want to see the difference between April and May, in the May row(leaving April blank) and in the June row showing the difference between May and June. Also, any chance this formula work at the employee level? I am interested to see the difference for each month for each employee without taking the amount from the other employee and calculating that but rather applying the first rule again.

Not sure I have been able to explain it properly but the end goal for this report is to see if an employee has a 20% increase/decrease in gross earnings to the current month from the

MSUM.PNG

previous month.

Thank you in advance