Sum Function for Data table

[AliciaDG]

Hi,

I have a data table that pulls data from a dropdown box and pulls information from a table.

What i am trying to do is figure out what the total number of Adults and kids where at a location.

Also i am stumped on how to have a date as a filter as well that is connected to the data table.

Thank you 

[WarrenBelz]

@AliciaDG ,

The Filter would be something like this

Filter(
   YourListName,
   YourCampusField='DDCampus Location'.Selected.xxxx &&
   YourDateField=DateValue(YourDateDropDown.Selected.xxxx
)   

where xxxx depends on the Items property of the control.

When you type YourControlName.Selected. (note second dot), valid values will come up underneath. For some guidance of what they mean: -

• If your Items are Choices(ListName.FieldName) - a Choices or Lookup field), the reference will be YourControlName.Selected.Value
• If they are Distinct( . . .  .), it will be YourControlName.Selected.Result
• The other option is when they are based on a field value in a list (other than Distinct), then it will be YourControlName.Selected.FieldName (actual field name)

If you wanted totals only in the gallery, you could use this as the Items

AddColumns(
   GroupBy(
      YourListName,
      "YourCampusField",
      "YourDateField",
      "OtherData"
   ),
   "AdultTotal",
   Sum(
      OtherData,
      YourAdultField
   ),
   "KidsTotal",
   Sum(
      OtherData,
      YourKidsField
   )
)

Are you in q'land?😎

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AliciaDG]

Thanks @WarrenBelz  for your reply

i did the add columns one you sent through. but still not getting any luck with it. Not sure what i am doing wrong

Not in QLD, in the NT 🙂 

Helping another location out

[WarrenBelz]

Hi @AliciaDG ,

Must be getting a bit hot now in Darwin - I am in Rocky.

Where are you putting this formula - it is valid as the Items of a gallery provided all your list and field names are.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AliciaDG]

HI,

Rocky nice never been will one day though. it is very hot up here now and climbing. 🙂 

I am putting the field in a text bar where i want the total to show up.

Not sure if it is meant to be somewhere else but that is where i want it to go 

Cheers

Very new to this learning as i go or trying to at least 🙂 

[WarrenBelz]

@AliciaDG ,

You can put that in the Gallery Items and then total the two new columns - the kids total would be

Sum(
   YourGalleryName.AllItems,
   KidsTotal
)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[WarrenBelz]

Hi @AliciaDG ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[AliciaDG]

Hi @WarrenBelz ,

Apologies, i wasnt able to relook at the data. I am still having issues will pulling info.

Currently what is working:

Dropdown box for location it pulls the data into the data table format. 

what isnt working is the date dropdown box and the adding up of the adults and kids from the data table.

it is probably something very simple but i just can figure that out.

[WarrenBelz]

Hi @AliciaDG ,

What current code are you using? Can you please supply as text along with any errors and what results you are expecting and getting.

[AliciaDG]

Hey @WarrenBelz ,

thanks for your continued help.

i basically want to get a tally of how many adults & Kids we had at a specific location on a specific date. 

i have the current formulas

Campus (Dropdown box) code: 

Distinct(Church_Online_1, 'Which Campus are you joining from?')

Date (dropdown box) code:

Distinct(Church_Online_1, 'Date of Service Joined:')

Data table code: 

Filter(Church_Online_1,'Which Campus are you joining from?'=Dropdown2.Selected.Result)

Label for Adult total code:

"Adult Total: " & Sum(
Gallery2.AllItems,
'How many adults in the Service?'
)

Label for Adult total code:

"Kids Total: " & Sum(
Gallery2.AllItems,
'How many kids in the Service?'
)

Currently what isnt working is when i click on the date dropdown but i cant get the filter for the date as well everything i try gives me an error. So havent figured that code yet.

the other thing that is not working is the totals for the adults and kids. it is just giving me the overall total for the whole document. i want it to corralate to the campus and date that i pick to pull the totals for those areas.

Hope all that make sense.

thanks