Question on Table/Calc Formatting

[Neil Scrivener]

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!

N 

 

 

 

 

[comment]

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

 

[Neil Scrivener]

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

 

[comment]

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

 

[Neil Scrivener]

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 !

 

[comment]

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 May 12, 2020 by comment

[Neil Scrivener]

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)? 

[comment]

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.

 

[Neil Scrivener]

Working great, thank you!