Extracting day out of date (and calculate a sum based on that day)

[Werner_1964]

Hi there,

I'm buiding an app where colleagues enter the sales of an item. The date of the sale gets registered when they book the sale. All data gets stored in an excel file located in onedrive.  The time period over which the sales are booked extends to over four or five months. Sales can be booked seven days a week. 

In the app I want to have a screen with some statistical data, like the number of sales booked on mondays, and tuesdays and...

In "normal" lingo, the question would be like "how many sales were booked on mondays, during the sales season".
So, I probably need some combination of "CountIf" with "Date = monday"...

I've found examples of code how to count and sum the number of items sold an how many sales.
But I can't find a good example of the code to extract the day out the date and count the number of sales on that day.

Any help or redirection to the sample code is highly appreciated!

G's

Werner_1964

ps: come to think of it... an overview of "sales booked in month X" would be nice to, but I guess the code for that will be a lot like the one for extracting the day out of the date?

[KroonOfficeSol]

@Werner_1964 

There is a Day() formula in PowerApps you could use here. Or add a column to your Excel table which calculates the day and refresh your data source connection in PowerApps to retrieve it.

Tip1:
Now you will get delegation errors and the app will be very slow if you build you're condition directly on the connection. Maybe you can better use the ClearCollect() to store the records from your Excel in a collection and use the collection in your formulas. 

Tip2:

Sales for several month and multiple salespersons will probably give a lot of records. So If you want to use this solution for a longer time you could maybe better move to a SharePoint list or better a SQL Server.

Hope this helps,

Paul

[RandyHayes]

@Werner_1964 

This is a formula you might want to use.  You'll need to adapt it for your data sources and column names (areas in blue😞

AddColumns(
   GroupBy(
      AddColumns(yourData, 
         "DayOfWeek", Day(yourDateColumn)), 
  "DayOfWeek", "DayData"), 
"SalesCount", CountRows(DayData))

At the end of this you will have a list of data with three columns:

DayOfWeek - the day number of your dates,

SalesCount - the count of the sales entries on that day of the week,

DayData - the actual records that match that day (0 to many)

You can use this formula for a Gallery, Collection, Charts or other places where you want to gather this information.

I hope that is helpful for you.

[Werner_1964]

Hi Paul from @KroonOfficeSol ,

Thanks for the tips!

I'm just starting out with PowerApps, and I see a lot of opportunities in this platform to automate some of our work in a fast way.  The booking of this sales is one of many (smaller) tasks that keeps getting done "on paper" while automation of this task would bring us lots and lots of gains.

About the collect-option: I'm certainly gonna look into that suggestion (Shane Young has a video on this, I've noticed).
About the amount of sales and sales people: we sell a booklet for about 4-5 months at our Visitor Reception area. So, all in all a limited amount of time and number of sales.  Sharepoint storage is an option (for this and other projects we have in mind), but I need to get my ICT-colleagues on board (they set up the Sharepoint environment) and they're not available on short notice while we start selling the booklet as of june.

But: your suggestions go into my little red book labelled "learning PowerApps". 😉

Kind regards,

Werner_1964

[Werner_1964]

Hello @RandyHayes ,

Thanks for the code.

I'm going to try out you suggestions later this day. I'll post back what the results are.

As stated in my reply to @KroonOfficeSol : I'm new and learning, learning, learning 😉

Kind regards,

Werner_1964

[RandyHayes]

@Werner_1964 

No problem.  It's fun to learn this stuff!!

I've attached a sample app that shows how to chart data. And some of the concepts of Grouping your data for statistical analysis.

There are 5 items in the sample,  2 bar charts with sample sales data grouped by the day of the week and, in one chart, counted, and in the other chart, the sums.

There's a Pie chart to give example of using that with the same data.  And there are two data tables that show the data both counted and the sums.

Things to look at:

1) The OnStart of the App.  Here you will find a Sample collection that feeds all the charts and tables.  This is what you would replace with your own data source.

2) The Items properties of all the Charts.  Here you will find the formulas used to get the data for the charts.  Charts are not super smart, so you REALLY have to "shape" your data for them.

3) The Items properties of the tables.  Here again you will find the formulas used for the tables.

There are no other actions, or properties modified for this sample to look at.

I hope that helps you some as you put your own app together and discover the endless possibilities of PowerApps.

EDIT: I should also mention, download this attached App to your computer, then open the PowerApps designer and open an App, then chose File->Open and browse to the downloaded App.