Calculating Percentage increase on records of different years.

[Miss A!]

Hi there,

 

I am struggling to build a calculation for percentage increase on records of prices between two years, records are marked with a value list field by year and season, HS(High Season) and LS(Low Season)

 

2015 HS

2015 LS

2016 HS

2016 LS

 

For each record there a re different price option fields, 1st Price, 2nd Price and 3rd Price. I want a calculation that checks if the 1st price field is not empty then calculates percentage increase for first price for the year 2015 HS record and 1st Price 2016 HS record, if it is empty then does calculation on the 2nd price for same season of different years(2015 & 2016)

 

I understand there is fun in filemaker calculations unfortunately that fun is taking too long o get to me

 

Always - Many Thanx!

[LaRetta]

Hi Miss A,

 

You can use the List() function which removes empty values.  I'm not quite sure how you are set up but see if this moves you forward:

GetValue ( List ( Price1 ; Price2 ; Price3 ) ; 1 ) * your percentage or other field

Since empty values are dropped, the first value, defined by GetValue ( field ; 1 ) will get the first non-empty value.

 

I should also mention that your prices should be records and not fields.  But then ... you already understand that.  :-)

[Miss A!]

Hi LaRetta,

 

Thank you for your attention.......

 

I definitely need to do some reading on the List function.......my level of having fun with calculations has been weak for too long and still goes on

 

I should also mention that your prices should be records and not fields. 

 

Unfortunately prices are fields, that is records are for lodges(unique ID defined) and the price fields for different meals and accommodation options e.g for a particular lodge i want to calculate percentage increase between prices of the previous year and prices of the current year(For each lodge entry, there is a value list field that defines Season and Year e.g"HS 2014, LS 2014,HS 2015, LS 2015,......." therefore a lodge have more than one record for each year and probably two records per year on the seasons) for the following options: DBB(Dinner Bed and Breakfast), BB(Bed and Breakfast) or FI(Fully Inclusive), in that order if the first field is empty(no price) the i should base calculation on the second fields' option and so on.....

[LaRetta]

I am sorry.  I cannot help you.  I do not have that kind of time.  Maybe someone else can.  I wish you the best on it.

[LaRetta]

If you can create a new file showing just the table/s involved with only a few of the fields (plus their IDs) of how the data is arranged then it will help others trying to assist you. :-)

[Miss A!]

At this point i am kinda lost to try anything if for help - will need time to find heart again, maybe i will figure then..........*Readings' in progress*

 

Appreciate your good effort and willing always!

[Wim Decorte]

 

Unfortunately prices are fields, that is records are for lodges

 

I would strongly advise to change that and put in the proper data architecture.  All the rest like reporting will just fall into place and will be much easier to maintain.

[bruceR]

Miss A!: here is what you are asking.

Your business is complex.

Compounding this problem, your database is a mess.

It is not properly designed and takes a LOT of work to get the results your business needs.

Because your database is a mess, you can't figure it out, and you want experts to try to waste their valuable time, working on your mess, for free.

Everybody can make their own decisions about whether to do hard, confusing, time-wasting work for you. For free.

But this data is how your business makes money.

They should pay for a developer to fix the database properly.

[Wim Decorte]

and part of that "paying" could be allowing you the time to learn and build from scratch.

[Lee Smith]

I agree with Wim,

Here is an article that anyone finding themselves in the situation he described above, might want to read this Thread by comment back on Feb 22, 2008 "Does anyone takes us seriously” QUICK LINK

[Rick Whitelaw]

On this topic I remember well the time I started using FMPA. The difference between you and me , Miss A, is that I realized almost immediately that I was facing a very difficult learning curve before I could reasonably expect to even begin thinking about creating a solution to help run my business. I had to work one step at a time. That said, I now have a solution that works quite well. I'm not a pro developer. All I want is to save time and drudgery doing "by hand" what could be accomplished using db technology.

What I'm saying is you've needlessly "thrown yourself into the deep end". You've been in over your head since you began posting on the forum. Google "database normal" and you'll find some very useful basic database theory. The participants on this forum cannot be expected to do your studying for you. As well, the threads I've read show me you regularly ignore advice given to you and then you post more questions that all arise from the same problem: bad design.

I'm not suggesting you stop asking questions. However you DO need to do some basic work on your own time. I suggest concentrating on simple problems and expressing them clearly including as much detail as possible.

My two cents,

Rick.

[LaRetta]

Miss A, as you can gather, there are many folks here who wish to help you succeed.  You shy from a 'restructure' immediately but you do not even know what it would take to do so.  Splitting fields into records is not THAT difficult.  Most of your reporting needs comes from a transactions table (this one?) and it is quite doable to split it properly.  You've had many offers of help but you dig in and respond that it can't be changed.

 

And now I have asked for a simple file with just the few key fields to produce your request (above) so we could at least TRY to create a method of retrieving the information you need and still you balk.  Your current solution is far too messed up for us to touch but a simple small file with 5-6 fields should be possible for you to create for us showing just the structure and fields.  If you can not do that then, please forgive my being blunt, but you really need to hire someone to help you.

 

You indicate that you are FileMaker Business Alliance and Platinum Member.  If so, the money needed to hire you help to restructure should be available.  Don't give up your dream.  Instead be strong and tell your company that you need to hire a developer to make structure changes.  It is not all your responsibility, girl.  Put it back on the business who gave you this impossible mess to begin with.  

 

We've provided you links to basic trainings on many threads here.  And read the link Lee referenced in post #10.  Sometimes businesses put too much on employees who lack background and it simply isn't fair.

 

edit ... corrected typo 'your' to 'you are'