Calculated filed formula required for this excel equivalent formula

Question & Answer
, ,
1

Hi,

I’m trying to create a new calculated field which works on row to row basis, I want to replicate this Excel formula in quick sight, please help.

Excel Formula in D2: =IF(A2=A1,D1+B2-C1,B2)

2

Hi @Chittaranjan - welcome to the QuickSight Community! Thanks for asking your question.

Can you please try the lag function?

Here is another post that’s similar to your ask: How to create calculate field to fetch previous value in column?

Please let us know if this addresses your need.

1 Like
3

Hi @Chittaranjan - Did this solution work for you? I am marking this reply as, “Solution,” but let us know if this is not resolved. Thanks for posting your questions on the QuickSight Community!

1 Like
4

Hi The solution is not working as it is a row by row calculation without aggregation, Please help in solving, the mentioned case.

5

Hi @Chittaranjan - I unmarked the reply as Solution. Can you please share some sample data and the expected results so that I can review? Thanks!

6

Hi @debapc,

Please find the attached screenshot, I’m trying to get the Load travelled in vehicle in each leg based on what had been loaded at departure location and unloaded at arrival location, For that the formula in F2 I used is =IF(A2=A1,F1+D2-E1,D2) , and the same is dragged down for F3,F4 and so on.

image
image1470×322 16.7 KB

7

Hi @Chittaranjan - I worked on your dataset. Here is the result

Screenshot 2023-10-05 at 7.14.38 AM
Screenshot 2023-10-05 at 7.14.38 AM1838×382 53.5 KB

Let me explain the steps …

  1. I added a column Seq_Num, which is a running sequence to the dataset
  2. Weight_Travelled is the value that you shared as “Physical Weight Travelled in the vehicle”. I copied the value to compare with the Calculated field Compute_Weight_Travelled.
  3. I added a Calculated field Dep_Weight_Loaded_Running = runningSum(sum({Dep_Weight_Loaded}),[{Seq_Num} ASC],[TripID])
  4. I added another Calculated field Arr_Weight_Unloaded_Running_Lag = runningSum(sum({Arr_Weight_Unloaded}),[{Seq_Num} ASC],[TripID]) - sum({Arr_Weight_Unloaded})
  5. Finally I added Calculated field Compute_Weight_Travelled = {Dep_Weight_Loaded_Running} - {Arr_Weight_Unloaded_Running_Lag}. Compute_Weight_Travelled is the field that you wanted calculate.

Please let me know if this works. Thanks!

8

Hi @Chittaranjan - Did the above solution work for you? Appreciate your feedback. Thanks!

9

Hi @Chittaranjan - I hope the above solution worked for you. I am marking the reply as “Solution”, but let us know if this is not resolved. Thanks again for posting your question on the QuickSight Community!

10

Hi @debapc ,

Thanks a lot, Apologies, I couldn’t check, but it worked,

1 Like