cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jjasper
Helper III
Helper III

Calculating values for multiple columns

Quick question... I hope. I have a gallery that I have set up to collect numbers. I have two labels that I want to show the pervious days numbers and then when the submit the new days numbers they subtract or add to the pervious days numbers.

 

Context: We have to report out our COVID numbers each day. I have two columns that have Total Active Resident Cases and Total Active Team Member cases. I would like this to change daily based on the numbers inputted. But when the screen loads I would like those two labels to populate with the pervious days numbers. Also when they submit the numbers I would like the new number from today to be submitted so tomorrow when they enter numbers they will show yesterdays new numbers. 

 

E.g. There are 6 new resident cases, 2 residents a resolved and 1 resident passed away these numbers need to be added or taken away from the previous day. So if we have a total of 10 active resident cases the day before the label would show 10 but when they submit the numbers it will change to 13 active cases. 

 

I am also planning on making a quick number dashboard in the app so if they number can't change until after submitted and form loads with the new number that is okay. It will show up on the dashboard to say they have 13 active resident cases. 

 

I really hope this makes sense. 

 

oh FYI my data is being held in SharePoint list.

 

Thanks

 

Jes

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@jjasper 

Yes, I was thinking this kind of logic:

- Previous Day Total would be the "truth" number.  And in fact, it wouldn't necessarily be "previous day" but more of a Previous Total in general as I can imagine situations where perhaps someone enters no new numbers for a particular day and for a particular record. (hunch)

 

- Today's Total would really be an "Add number".  This would be the amount to add (positive or negative) to the "Previous Total"

 

- A date column would also exist that specifies the "Previous Total" date.

 

- When a user accesses a record, the app would look at the Previous Total Date.  If it is Yesterday's date, then the User would see the previous total.  The value of the "Add Number" column would be shown and editable. 

 

- When a user accesses a record, the app would look at the Previous Total Date.  If it is Older than Yesterday's date, then the app would update the record with the Previous Total plus the Add Number total and also update the Previous Date to Yesterday's date, it would finally zero the Add Number column.  Then the User would see that updated previous total.  The value of the "Add Number" column would be shown and editable. 

 

So the above would work well, except accommodations would need to be made for reporting - reporting for a total would always be the addition of the previous total and the Add Number value to be accurate.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

9 REPLIES 9
RandyHayes
Super User
Super User

@jjasper 

Yes, this can all be done in PowerApps, but I have question about the previous and today numbers.  Do you have a previous and today number in your list?  I ask because you said you want the prior day numbers to be displayed and for changes to occur for today number.  This is all fine, but you will need to have those two columns and also some column as an indicator of which date the today number represents in the list.

Let me know if this is what you have or if this is what you are planning, or if this is not in the works.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
GarethPrisk
Resident Rockstar
Resident Rockstar

Have you considered a data model, that is based on each individual Case? Instead of relying on simply numeric fields. Specifically, if you enter/track a Case record, then you'll be able to understand the volume of cases, when they were new/resolved/deceased, and other metadata.

 

For example, a list of Cases, with columns:

  • Patient Name
  • Positive Date
  • Resolved Date
  • Deceased Date

This is very simply, by design. This allows you to simply

  1. Create a Case, for a particular person, on any date (back-dating is possible)
  2. Update a Case
    1. Search for particular person
    2. Enter Resolved Date or Deceased Date

Then reporting-wise, you can show the case load over-time. You can also derive length of cases, resolution (%), etc.

 

I currently have a column that updates based on Today() date. The only column I have is Total Active Resident Cases. I don't have a column for previous or today. If needed I can add those in. 

 

I have 10 columns in the SharePoint list. Village, Neighbourhood, Date, # of New resident cases, # of resolved resident cases, # of residents who passed away, # of New team member cases, # of resolved team member cases, Total active resident cases and total Active team member cases. 

This is a direction we will be headed in the new year but it takes a lot of manual work to begin creating an app like this. We need something now and we aren't ready to go that way just yet. 

RandyHayes
Super User
Super User

@jjasper 

So the need for a previous day number is going to be based on this part of your original post - 

I have two labels that I want to show the pervious days numbers and then when they submit the new days numbers they subtract or add to the pervious days numbers.

So, if the user selected a record and saw "the previous day" numbers and then submit new day numbers...the question is this - if they come back to THAT record again, they would see today's numbers as the number (since the calculation has been done).  

From your post, it sounds to me that you would be wanting a previous day number column (so if they open that record again, they would see previous and today and could edit today numbers if necessary) and a today number record that is the calculation of additions or subtractions for the day.

 

Am I swinging in the right ballpark or playing in another game?

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

oooohhh... I never thought of that and yes you are definatly swinging in the right ball park. I would love to be able to track the net change in numbers eventually. And with what your saying that could be done. So yes I would need a pervious day column with the numbers from the previous day and then a column that has the new numbers then we could easily track the net change. Sorry if I went in a whole other direction.  

 

 

RandyHayes
Super User
Super User

@jjasper 

Yes, I was thinking this kind of logic:

- Previous Day Total would be the "truth" number.  And in fact, it wouldn't necessarily be "previous day" but more of a Previous Total in general as I can imagine situations where perhaps someone enters no new numbers for a particular day and for a particular record. (hunch)

 

- Today's Total would really be an "Add number".  This would be the amount to add (positive or negative) to the "Previous Total"

 

- A date column would also exist that specifies the "Previous Total" date.

 

- When a user accesses a record, the app would look at the Previous Total Date.  If it is Yesterday's date, then the User would see the previous total.  The value of the "Add Number" column would be shown and editable. 

 

- When a user accesses a record, the app would look at the Previous Total Date.  If it is Older than Yesterday's date, then the app would update the record with the Previous Total plus the Add Number total and also update the Previous Date to Yesterday's date, it would finally zero the Add Number column.  Then the User would see that updated previous total.  The value of the "Add Number" column would be shown and editable. 

 

So the above would work well, except accommodations would need to be made for reporting - reporting for a total would always be the addition of the previous total and the Add Number value to be accurate.

 

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Okay that makes sense. 

RandyHayes
Super User
Super User

@jjasper 

Very good...I would say to go down that road and implement the best you can.  Post back here for any more details or problems you run into.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (2,088)