Calculating format premiums

[enquirerfm]

I'd thought I'd solved this but looking at the background data it's not calculating properly. Any ideas much appreciated.

I have a file with a date, bottle format, total cost and converted cost for larger formats (so, for example, 75cl might cost $20 but 1.5l costs $45. The converted cost of the 1.5l is $22.5). Assuming one beverage and just one bottle of each, I want to output a report which looks at the costs and converted costs over a specified period (eg 1/1/2016...12/31/2016) and outputs a 'premium' for, say, the 1.5l bottle.

In this example, let's say I have 6 records:
date1 75cl $19
date2 75cl $19.5
date3 75cl $20
date4 1.5l $43
date5 1.5l $45
date6 1.5l $48

Then searching between date1...date6 would give etc.

However, it is not working out the totals correctly eg it is not calculating the premium properly for the 1.5l format - it appears to group all of the values together.

Edited July 5, 2017 by enquirerfm

[comment]

I am afraid this is clear as mud. 

First, you tell us nothing about what you did - so we are in no position to say why it is not working as you expect. Even worse, you tell us nothing about what you want to do. I for one don't know what you mean by a "premium" and have no clue how to calculate such a thing. 

 

Edited July 5, 2017 by comment

[enquirerfm]

So.. if you look at the values of 1.5l they are higher than the 75cl because they are twice the size eg dividing by 2 :

date4 1.5l $43 converted size 75cl = $21.5 per 75cl
date5 1.5l $45 " = $22.5 per 75cl
date6 1.5l $48 " = $24 per 75cl

and:

av of 75cl prices = $16.67

av of 1.5cl prices = $22.67

'PREMIUM' = (22.67-16.67)/16.67 = 40%

I created a table which had summary values for those that I found but can't figure out how to create the denominator.

[comment]

Getting the average cost of each size is easy: you only need to group the found records by size, and place a summary field in the sub-summary part. Comparing the sub-summary values of two (or more) groups is more difficult, because Filemaker processes each group separately and there is no way to access a sub-summary value of one group from the context of a record in another group.

One way to solve this is to have your script populate a variable with the average cost of the 75cl group, then use this variable to calculate the "premium" of the other group/s.  If records are sorted so that the 75cl group is the first one, you can populate the variable by going to the first record and doing:

Set Variable [ $$baseCost; Value: GetSummary ( sAverageCost ; Size ) ]

This will enable you to calculate the "premium" as =

 ( GetSummary ( sAverageCost ; Size ) - $$baseCost ) / $$baseCost

Of course, this can only work if there is at least one 75cl record in the found set.

 

Edited July 5, 2017 by comment

[Steve Martino]

Please...Thank you.....give them a try OP.

[enquirerfm]

Thanks for your help. So, I created 2 new fields - let me use my naming - p_sAverageCost and p_denominator

p_sAverageCost is a Summary field calculating the average of the cost field I am trying to find (it does this fine)

I have set p_denominator as a Calculation field with the formula:

(GetSummary ( p_sAverageCost ; Bottle size ) - $$p_denominator ) / $$p_denominator (I have ticked box saying Do not store the result and it is not stored in the global field either)

I have created a report with a script:

GotoLayout (that I want)

Sort (to give the correct base wine price based on std bottle sizes)

Set Variable - $$p_denominator | Value: GetSummary (p_sAverageCost;Bottle size)

Enter Preview Mode

However, the report outputs the sub-totals fine for each format but the formula and $$p_denominator fields are zero. Any idea where I went wrong?

[comment]

1 hour ago, enquirerfm said:

Any idea where I went wrong?

No. Does this work for you?

 

Premium.fmp12

[enquirerfm]

Thank you very much for the file. I have implemented it and it seems to work fine for a selection where there are only 2 formats, but when one introduces more eg ad in a 3l and 5l the calculations don't work. In fact the first calculation, the base price is wrong... then it follows all the others are too but I'm not sure if that is the whole story because without checking it with a correct base price I can't see how the other figures are calculated.

I am attaching the spreadsheets so you can see how the DB calculates the figures and the actual figures it should have calculated.

Thank you again for taking the time - much appreciated.

 

 

Edited July 12, 2017 by enquirerfm
revised files

[comment]

2 hours ago, enquirerfm said:

the calculations don't work. In fact the first calculation, the base price is wrong...

That's a pretty useless description, I am afraid. It would be much more productive to include an example of some data and show how the actual result differs from the expected one.

Still, I have a guess:

On 7/5/2017 at 5:43 PM, comment said:

 If records are sorted so that the 75cl group is the first one, you can populate the variable by going to the first record and doing:

Set Variable [ $$baseCost; Value: GetSummary ( sAverageCost ; Size ) ]

My guess is that after you have added other sizes, the 75cl group is no longer the first one - so you need to find another way to populate the variable (for example by constraining the found set to only 75cl records).

 

[enquirerfm]

Just now, comment said:

That's a pretty useless description, I am afraid. It would be much more productive to include an example of some data and show how the actual result differs from the expected one.

Still, I have a guess:

My guess is that after you have added other sizes, the 75cl group is no longer the first one - so you need to find another way to populate the variable (for example by constraining the found set to only 75cl records).

 

so you didn't look at my files?

[comment]

Actually I did, but they didn't tell me anything useful either. There is only so much time I can dedicate to this - please don't expect me to spend it hunting for information that you can supply directly.

[enquirerfm]

So... I fixed this (my way) by writing the variable to a global field and picking this up for the calculation.