Question on Table/Calc Formatting

Recommended Posts

Hi Guys,

I may have gone about this the the wrong way, but I have a table that looks like this, with quarter data:

 Q1 Data Q2 Data Q1/2 Difference % Q3 Data Q2/3 Difference % Q4 Data Q3/4 Difference % 10 12 20% 15 25% 20 25%

Each Record in this Table is the Year. So I have a Record for 2017, 2018, etc.

This is all working perfectly on a snapshot basis.

What I now want to do is add calculation field (I think?) which 'tracks' the Quarters.

So for example if there is Q1 and Q2 data, it would show me the difference between Q1 and Q2.

But when there is Q3 data, it should now show me the difference between Q1 and Q3.

And when there is Q4 data the difference between Q1 and Q4.

So the end of the year, the information is clear between Q1 and Q4, but along the way it's relevant to the quarter.

Bearing in mind I am a total numpty to Filemaker, and still learning, can anyone advise the best way to do this?

I suspect there will be the suggestion that there should even be another Table for Q's, and then you'd link the Q's by the year?! Ideally, I REALLY don't want to do this, if possible!

So, if that's as clear as mud, can anyone help me?!

Thanks!

Share on other sites
10 minutes ago, Neil Scrivener said:

I suspect there will be the suggestion that there should even be another Table for Q's, and then you'd link the Q's by the year?! Ideally, I REALLY don't want to do this, if possible!

I am not sure why you "don't want to do this", but if you don't want to, you don't have to. However, you will eventually run into the limitations of denormalized structure.

Still, for now you could calculate the difference as =

`GetValue ( List ( Q4 ; Q3 ; Q2 ; Q1 ) ; 1 ) - Q1`

Share on other sites

Hi

Thanks for coming back to me on this.

The reason I don't want to have the other tables (yet) is just due additional work involved in Reporting, as I'll want to compile the Q's into Year, and then have an overall picture. I figure this could get complex?

I will be changing it as a next run, but for now, I want to do it with dummy data and see that I am getting the correct information, as is, and I can improve on things once I know it's working correctly.

Turning to your calculation, what is the exact calculation you've provided me with, or do I need to add other information?

Currently, if I put the following data in (copying and pasting your calculation), I'm not getting the figures I'd expect.

Q1 = 10, Q2 = 12 is giving me a total difference of 200%! (difference should be 20%)

If I then add data for Q3;

Q1 = 10, Q2 - 12, Q3 = 15 I'm getting a total difference of 500% (difference should be 50%)

Q1 = 10, Q2 - 12, Q3 = 15, Q4 = 17 I'm getting a total difference of 700% (difference should be 70%)

What have I done wrong?

Thanks!

N

Share on other sites

Well the difference between 12 and 10 is 2. If you format it as percent , you will get 200%.

Apparently. you want percentage change, so the formula needs to be modified to:

`( GetValue ( List ( Q4 ; Q3 ; Q2 ; Q1 ) ; 1 ) - Q1) / Q1`

Share on other sites

That is great, thank you so much

One other question, which is (sort of) similar.

How do I make a calculation to 'read' my latest Q.

So for example, if I have data in Q1, but then put data in Q2 - it reads Q2, and 'forgets' about Q1. It never adds or subtracts, it only looks for the 'last' Q with data in.

Does that make sense?

Thanks for all your help !

Share on other sites
Posted (edited)
57 minutes ago, Neil Scrivener said:

How do I make a calculation to 'read' my latest Q.

Isn't that exactly what this calculation does? It creates a list of all 4 values that are not empty, in reverse chronological order:

`List ( Q4 ; Q3 ; Q2 ; Q1 )`

then gets the first value from this list.

Edited by comment
Share on other sites

Thank you  I'm still learning BIG TIME

This should get me going, but I'm afraid I know I'll be back with questions on Reporting!

40 minutes ago, comment said:

Isn't that exactly what this calculation does? It creates a list of all 4 values that are not empty, in reverse chronological order:

```
List ( Q4 ; Q3 ; Q2 ; Q1 )```

then gets the first value from this list.

Sorry, it doesn't get the first value, it actually puts them in front of eachother.

So if I have Q1-47, Q2-13 and Q3-8 - it shoes 47138.

How do I fix that to show only 8 (ie the 'last' thing put)?

Share on other sites
25 minutes ago, Neil Scrivener said:

So if I have Q1-47, Q2-13 and Q3-8 - it shoes 47138.

How are you testing this? If you have Q1 = 47, Q2 = 13 and Q3 = 8, then:

`List ( Q4 ; Q3 ; Q2 ; Q1 )`

will return:

```8
13
47```

and getting the 1st value of this list:

`GetValue ( List ( Q4 ; Q3 ; Q2 ; Q1 ) ; 1 )`

will return 8.

Share on other sites

Working great, thank you!

Create an account

Register a new account

• Similar Content

• By Spidey
Hi,
I have two table: Invoice and Customer.  I like to have the total of all the invoice for a customer between certain date in the Customer portal that show all the customers, but I got a error when I try to debug..
ExecuteSQL("SELECT SUM(I.TotalAmount) FROM Invoice I JOIN Customer C ON I._kf_CustomerID = C.__kp_CustomerID WHERE date(I.InvoiceDate) between date(C.SearchFromDate ) and   date(C.SearchToDate )" ; "" ; "" )
I have an error and couldn't figure it out.  Thanks...
KC

• I have a client that has been using a send email script step  that brings up the outlook email client on the desktop.  This as worked for years no problem.  It has stopped work on 3 of 35 computers within the last two weeks.  I talked with there IT personal and they have assured me that no updates have happened.  The actual error is -
Microsoft Office Outlook
Either there is no default mail client or the current mail client cannot
fulfill the messaging request.  Please run Microsoft Outlook and set it as
the default mail client.

I have double checked with system default  and Outlook's settings.  Both are set to default.
Any suggestions are welcome.

• I have an excel sheet that controls bills of ladings for a forestry company.  In the example you can see that there is lots going on with this Bill.  It has a payperiod, mill, truck that delivered it, etc.
I would like setup a database to monitor this.  The fields CT1, CT2, Skid1, Skid2. PROC1, PROC2 are all contractor numbers.  There are 6 contactors.  The percentages in each line are the amount of the volume they performed  In the third line there is a value in CT1 only...they get 100% of the volume.  I can figure out most of this, but am stumped on how I can monitor when a contractor does multiple jobs..ie in line one, contractor 5, cuts and skids.  All 6 contractors could be involved in one BOL. Each one of these jobs, cutting, skidding and processing each has their own respective rate of pay as well.   I think i need a way to break down each line so that I can produce pay summaries for each of the contractors.  I had started this years ago, and thought I asked in a forum, but can't remember where.  Nonetheless, they stopped using multiple contractors per load...Now they have returned, so I am back at it.  So if this is a repost from years ago I apologize.
tbcomputerguy

• I am using Filemaker Server 18 on Windows Server 2012 R2
Been using it for years with no issues
Currently when I log in to the console it is very sluggish.
When I get to the Dashboard it shows No databases, then it auto refreshes and the database list appears.
Within 15 seconds of scrolling the database list to open files the screen refreshes. This situations is happening over and over in a loop.
Any Thoughts on what is causing this issue?

• I get an error 3 when using a script to Export Records via WebDirect. Using FileMaker Server 18 and have tried both Safari and Chrome both with same results. I have tried using the temporary path, desktop path, and documents path. I have tried using with the automatically open and not. I have tried writing a tab delimited and comma delimited file. Does anyone have ideas I haven't yet tried?

×
×
• Create New...