Jump to content

Calculation to get Total Revenues broken down by Year


Recommended Posts

I know the solution is probably very easy but it escapes me. I got this database, which I use to create invoices, year after year, I created a report page where with a neat summary field I get the "lifetime revenues", "lifetime general expenses", "lifetime total taxes" and "lifetime total invoices number", which is handy, but I need to have the same data broken down by year, and I can't figure out how to achieve that.

In another post I have been suggested to consolidate the three tables I had (Invoices 2018, Invoices 2019 and Invoices 2020) into a single table (Invoices) and then use a "subsummary" which would give me the data I want based on how I sort the records but I am not sure how to use subsummaries nor I am sure I want something where I have to multiple click to re-order records to get data one at a time; I'd rather prefer a report where a calculation of some sort would make a sum of the total revenue of my invoice records broken down by year, as I exemplified in the database I'm attaching, is that possible in an easy, possibly not involving complicated subsummaries way?

I'm beginning to get desperate on the subject, please have mercy. :)

Invoices Demo.fmp12

Link to post
Share on other sites

There are a couple of ways to do this, but using a calculation or summary probably wouldn't be my choice.  The old invoice data is static and shouldn't have to be recalculated every time you want to see the numbers.  So consider creating a "totals" table and at the end of each fiscal period, compile the numbers for the year or period closing and post them to that totals table.

That will make reporting on them very fast since you wouldn't need to recalc.

Link to post
Share on other sites
On 12/12/2020 at 3:07 PM, comment said:

See if this basic demo helps you figure it out.

---
P.S. This is a continuation of your previous topic and should have been posted there.

SummaryReport.fmp12 168 kB · 4 downloads

I tried to make my DB work based on your example (it took me a while to figure out there was a script involved, something I never used so far) but I am still having some unidentified problems, even though (I think) I replicated the script and the table/fields, in particular my sub-summary by cYear is "Trailing" while yours is "Leading" and I have no idea how to make mine "Leading", nor I know if that is the mistake that prevents me to achieve what happens in your example, in your example I get a neat serie of fields each with years and totals such as (2018 - 1.000$ ; 2019 - 5.000$
; 2020 - 10.000$) and then the grand total (26.000$); but in the file that I replicated the only thing that really work is the grandtotal which is correct but just because it is a summary of all records, on the subsummary part I only get the grand total again on one line and I lose the specific for each year. Also, on my file I get a weird warning about a "Script Paused Continue/Cancel", which I also don't know if it impacts the behaviour of my database, I'm including screenshots for clarity.

On 12/12/2020 at 3:50 PM, Wim Decorte said:

There are a couple of ways to do this, but using a calculation or summary probably wouldn't be my choice.  The old invoice data is static and shouldn't have to be recalculated every time you want to see the numbers.  So consider creating a "totals" table and at the end of each fiscal period, compile the numbers for the year or period closing and post them to that totals table.

That will make reporting on them very fast since you wouldn't need to recalc.

You mean calculate manually the totals for each fiscal period and put them manually on another table? Or did I misread? That would be an idea but I'd rather have the calcs done by the software which is safer than me inputing manually the data.

AAA.png

BBB.png

Link to post
Share on other sites
24 minutes ago, Mafia2020 said:

it took me a while to figure out there was a script involved, something I never used so fa

 

Definitely look at scripted solutions for updating totals.  FM is more than a spreadsheet so it doesn't need to always work with nothing but calculations and summaries.  While that is easy and get you going quickly, those things turn out to be serious performance impediments as your solution grows.  Scripted workflows will bring tremendous power to your solution.

Link to post
Share on other sites
30 minutes ago, Mafia2020 said:

my sub-summary by cYear is "Trailing" while yours is "Leading"

If the layout does not have a body part, then it makes no difference (at least not in this case).

30 minutes ago, Mafia2020 said:

on the subsummary part I only get the grand total again on one line and I lose the specific for each year.

Obviously, you're doing something wong - but I cannot tell what it is based on a screenshot.

30 minutes ago, Mafia2020 said:

on my file I get a weird warning about a "Script Paused Continue/Cancel",

In my file, the script is paused to show the report. When you resume the script, it returns you back to the starting point. This is just for the convenience of the demo; you can change this to suit your preferred workflow.

 

Edited by comment
Link to post
Share on other sites
18 hours ago, comment said:

Obviously, you're doing something wong - but I cannot tell what it is based on a screenshot.

In my file, the script is paused to show the report. When you resume the script, it returns you back to the starting point. This is just for the convenience of the demo; you can change this to suit your preferred workflow.

 

That's fair enough. I hoped my mistakes where easy enough. I'm attaching the demo file, could you point me where the error(s) is. This thing is driving me crazy. I want to accomplish what you were able to. Please, be patient with me, it's my very first time delving with scripting in FM.

Invoices Demo.fmp12

Link to post
Share on other sites

I didn't mean I wanted to debug your file....

Anyway, I see two mistakes in the file you posted:

  1. Your script is sorting by the Data field, instead of the cYear field;
  2. Your report layout is in Form View instead of List View.

 

Link to post
Share on other sites
54 minutes ago, comment said:

I didn't mean I wanted to debug your file....

Anyway, I see two mistakes in the file you posted:

  1. Your script is sorting by the Data field, instead of the cYear field;
  2. Your report layout is in Form View instead of List View.

 

I'm sorry, I didn't mean I felt entitled to get my DB debugged, I was just growing desperate about the issue, anyways, thank you for sharing your knowledge, it may be something trivial in relation to your skills but that problem really buggered me for days and your two remarks really got me going, it now works as intended. I have a followup question on the same matter, if you could advise me on it, I'd like to add a field for each year where it would calculate the % difference in yearly revenue, normally I'd go with a calculation field which compares two numbers in two different fields and gives a % as a result, but I am not too sure that thing would work with subsummaries and sorting involved since the numbers I wish to compare are shown based on the records ordered by a date rather than being fixed.

Link to post
Share on other sites
3 hours ago, comment said:

Do you mean compared to the previous year's revenue? 

 

Exactly. Something like, adding to the existing scheme

2018 - 1.000€
2019 - 1.500€
2020 - 3.000€

2018 - 1.000€ - 0
2019 - 1.500€ - +50%
2020 - 3.000€ - +100%

Link to post
Share on other sites

Unfortunately, this is not so simple. It requires adding (at least) two fields for each value being compared. I have amended my previous demo to show how it can be done. Note that here it does matter that the sub-summary part is leading (there is a way to make it work with a trailing sub-summary too, but that requires adding even more complexity).

You can see that with so many fields, a summary table as suggested by Wim DeCorte gets more attractive. However, that brings its own complexity: you would need to plan very carefully when and how each year gets added to such table, and how to handle the current year's data in the report. I would hesitate to recommend such undertaking to a novice.

OTOH, it should be pointed out that if you chose a visual comparison instead of calculating the change in percentages, the built-in charting tool provides a relatively easy way to do so.

 

SummaryReport+Comparison.fmp12

Link to post
Share on other sites
15 hours ago, comment said:

Unfortunately, this is not so simple. It requires adding (at least) two fields for each value being compared. I have amended my previous demo to show how it can be done. Note that here it does matter that the sub-summary part is leading (there is a way to make it work with a trailing sub-summary too, but that requires adding even more complexity)

 

I managed to correct the leading/trailing, apparently you choose that on subsummary creation when you're asked if you want it printed above (which corresponds to leading) or printed below (which corresponds to trailing). Once I did that and replicated your suggestions it now works as advertised. You're truly helping me out man, I can't tell how invaluable your help is being on the matter. Now I may even venture into charting territory, another thing I could NEVER pull out with Filemaker.

Edited by Mafia2020
Solved the leading/trailing
Link to post
Share on other sites
  • 3 weeks later...
On 12/17/2020 at 7:56 PM, comment said:

Unfortunately, this is not so simple. It requires adding (at least) two fields for each value being compared. I have amended my previous demo to show how it can be done. Note that here it does matter that the sub-summary part is leading (there is a way to make it work with a trailing sub-summary too, but that requires adding even more complexity).

You can see that with so many fields, a summary table as suggested by Wim DeCorte gets more attractive. However, that brings its own complexity: you would need to plan very carefully when and how each year gets added to such table, and how to handle the current year's data in the report. I would hesitate to recommend such undertaking to a novice.

OTOH, it should be pointed out that if you chose a visual comparison instead of calculating the change in percentages, the built-in charting tool provides a relatively easy way to do so.

 

SummaryReport+Comparison.fmp12 168 kB · 4 downloads

I got a followup question. If I wanted to use the value obtained in each subsummary as a variable in a new subsummarized calculation, which road should I take?

So, for instance, currently, thanks to your input I got a report page which displays total revenues like this:

2018 - 6.000
2019 - 12.000
2020 - 24.000

I'd like to add a calculated field to each row where the variable would be the subsummarized result divided by 12 in order to get a montly revenue (so in that case it would be 6.000, 12.000 and 24.000 / 12 that would get me 500, 1.000 and 2.000). Sorry for asking something that trivial but I am still a little confused about subsummaries. Perhaps a LET function would do?

Link to post
Share on other sites
1 hour ago, Mafia2020 said:

to use the value obtained in each subsummary as a variable in a new subsummarized calculation

That's what the GetSummary() function does.

 

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Similar Content

    • By fousthvk
      Does anyone know how to do this? calling the "generate as configured" script stacks the script that gets called after the webveiwer is set at the end of the primary script, there for only the last record in the found set of the loops actually has the image file created and saved to the container field.
    • By bgscott
      Recently converted a FM application from V6 to V19.  The application will run on FM server.  Very new to FM19
      Questions related to temporary path
      I have scripts that exports data to 3 temporary fm12 files.  Two of the temp files hold data that is them imported to other files and one of the temp files holds data for a lookup in another file.
      dhistory imports data from dtemp
      rhistory imports data from rtemp
      vendors looks up data from vtemp
      The temp files are fm12 files
      Questions
      1.  I would like to have the temp files sent to the temporary path on the users computer.  I have not been able to correctly specify the output path to send the temporary files to the temporary folder on the users computer.  This is one attempt: get(temporarypath) & dtemp but this does not sentd the file to the temporary folder
      2.  Once I get the correct syntax to save the temporary files in the temporary folder, how do I tell the import records script step where to find the temporary files?  What is the correct way to point to the file so it would work on any users computer?
      3.  Vendors file performs a relookup in vtemp, so I need a relationship between vtemp table, located in temporary folder, and vendor table.  How do I specify the location of vtemp so it can be found on any users computer?
      Once the script finish running the temporary files are no longer needed and it is my understanding that the temporary files will be deleted when the Filemaker session is closed.
    • By milky
      Hi All,
      Just had a look at the new Add-ons in Fm19.
      Is there a way to make the timer work in reverse.
      i.e. Start from a field (Timer::TimeLength) that a user can input, so that the specified time of say 30:00 will run down to 0:00
      Any help would be appreciated.
      Regards
       
      Milton.
    • By BostonFM
      I am trying to script an import of three worksheets in the same Excel file. The 3 geographical regions worksheets are not structured the same, i.e. field name rows are not in the same place and are named differently.
      The script runs but stops to ask which worksheet to process.
      How do I create a variable/parameter in the script for it to proceed sequentially from worksheet 1-3 without stopping?
      Thanks
      DA
    • By SupportGuys89
      Hello Fellow FM'ers!
      We have been testing Linux and everything has been great, but we are having issues with the authentication via web direct using Windows Federated Services. We followed the steps in the available documentation "Addendum4_ADFS.pdf" and also used "ExtendOAuth_v1.pdf" to get the AD-FS rules configured as well as the necessary admin console requirements (Client Key, Shared Key etc...) We do see the following on attempting to login to our solution:

       
      And when we click on the AD FS Option we do receive a login prompt from our Federated Services:
       

       
      However, even with the correct credentials we still see the following error message:

       
      We have reviewed the logs within AD-FS and no errors are reported and the credentials are passed as expected. Within our solution and Active Directory, we also ensured the user is in the correct group to access our solution via webdirect. I will gladly provide more details into the configuration of AD-FS or FM Admin Console, but these all match the documentation provided by Claris. 
      We went a bit further and got an inhouse developer to take a look and test the flow and he was able to confirm the following:
      Our Endpoints are setup correctly and can be viewed internally @"https://INTERNALADFS/adfs/.well-known/openid-configuration" Initial request  was tested with "https://oidcdebugger.com/"  Postman was then used to test once we received a response. Decoding the jwt received with "https://jwt.ms/"
        The JWT had a unique_name, an email, and a groups array with one of the values in that group array being the group that is set up in the Filemaker solution.   With that said, we are not sure what the next step would be to test this as we appear to be seeing all the correct information in our testing yet we still are not being "authenticated" in our solution. I have also opened a ticket with FIleMaker and no luck as of yet. Any insight would be greatly appreciated!  
  • Who Viewed the Topic

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.