Answered

Tricky Sum from a filtered list using a LookUp to different list.

(0) Share

Report

Posted on by StephenGW

583

I have two SP lists similar to what is seen below although they will grow in length. I need to Sum some numbers from one list that is filtered and give the totals based on another list. I will try to show my desired output as well.

SP List 1

Code	Reason
1	Broken
2	Missing
3	Bathroom
4	Maintenance
5	Sleeping

SP List 2

Code1	Minuets1	Code2	Minuets2	Code3	Minuets3	Code4	Minuets4
1	15	1	10
3	10	1	15	2	10	4	10
1	15
5	5	3	15

Desired Output: Filter SP List 2 by some boxes. That is done like this code courtesy of Warren Belz (Thank you so much!) Then sum all the minuets based on the code/reason from SP List 1

Filter(
   SP List 2,
   (
      Value(DatePicker1.SelectedDate) < 1 ||
      DateMade >= DatePicker1.SelectedDate
   ) &&
   (
      Value(DatePicker1_1.SelectedDate) < 1 ||
      DateMade <= DatePicker1_1.SelectedDate
   ) &&
   (
      Len(ComboBox3_2.Selected.Value) = 0 ||
      Hour = ComboBox3_2.Selected.Value
   )&&
   (
      Len(ComboBox3.Selected.Cell) = 0 ||
      Cell = ComboBox3.Selected.Cell
   )
)

	
			Reason
			Minuets
		

			Broken
			55
		

			Missing
			10
		

			Bathroom
			25
		

			Maintenance
			10
		

			Sleeping
			5
		

 

I would like to output this into a chart at some point so I think a gallery or data table would be the best output?

Any help or well wishes are welcomed!

Categories:

General Questions

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Nandit 1,568 Moderator on at

Like (0)

Report
Tricky Sum from a filtered list using a LookUp to different list.

Hi StephenGW,

I created two collections in my app which are mimicking the data you have.

SPList1

SPList2

Here's the code I have developed for the Items property of a Column Chart:

Table( ForAll( SPList1 As Items, { Reason: Items.Reason, Minutes: Sum( Sum( Filter( SPList2, Code1 = Items.Code ), Minutes1 ), Sum( Filter( SPList2, Code2 = Items.Code ), Minutes2 ), Sum( Filter( SPList2, Code3 = Items.Code ), Minutes3 ), Sum( Filter( SPList2, Code4 = Items.Code ), Minutes4 ) ) } ) )

The above code produces the below Column Chart in my app:

Hoping this is what you are after.

Kind regards,

Nandit

If this answers your query, please mark this response as the answer.
If its helpful, please leave a like. Thanks!

Was this reply helpful? Yes No
StephenGW 583 on at

Like (0)

Report

Tricky Sum from a filtered list using a LookUp to different list.

Thats awesome Nandit! Thank you for the solution!

Was this reply helpful? Yes No