summary charts with different number of items looks odd

[Aussie John]

I often use summaries with a bar chart where the number of data categories I'm charting vary from one summary to the next. This means sometime bars are very wide with few categories or narrow if a lot. It there any way around this - ie tricking the chart to think there are more - but empty categories? I don't want to add dummy records.

Also is there any way to restrict the number of values on the y axis. I only need 0, 50% and 100% not 0,10,20,30,… or worse 0,4,8,12,…which seem to be the defaults depending on the chart size.

[Aussie John]

item - 2 - use ticks controller - but note it doesn't work if the max is 1 or below

[comment]

See if this helps:

UniformCharts.fp7.zip

[Aussie John]

Thanks that is the effect I am after. Very clever.

Unfortunately my DB uses separate records and sub summaries rather than your example which uses a list of values in each record (and a data source as current record and delimited data). AFAIK summaries can't be listed as this requires indexing. If it is any help I've attached my file for reference. thanks

booking data test.fmp12.zip

[comment]

Ahm.... for those of us that are a bit behind, could you attach a .fp7 version?

[Aussie John]

eeek sorry - thought you might have been blessed with at least a trial version. Here is hurried fp7 version (had to build from scratch so hope all ok).

chart test.fp7.zip

[comment]

I see. I believe you could use my method if your data were nomalized into Schools -< RoomTypes -< Rooms. Otherwise you'd need somewhat complex scripting and/or relationship building.

thought you might have been blessed with at least a trial version.

Alas, it's the OS that needs upgrading.

[Aussie John]

I don't really want to go down the three table route so I have made a field with a list of the frequencies (by school) but can't get the average. Am I flogging a dead horse here? Thanks.

[comment]

Am I flogging a dead horse here?

I don't think so, but there's a lot of flogging to do. Since you want to supply the x-axis values yourself (in order to tack on empty values at the end), you must also supply the y-axis values in the form of a return-separated list.

[Aussie John]

I have created a field with a list of frequencies applicable to the school and the Room type and am trying to calculate the average using a custom function. It is not working but here it is:

Let([

VC=ValueCount(F);

i=1;

V=GetValue ( F ; i);

i=i+1];

(V+Case(i-1<=VC; thelist(F))/VC)

)

where F is the frequency list and theList is the function

thanks once more

[comment]

I am not sure what your function is trying to do. IIUC, you need to generate a list of averages for each group (school), containing one average value for each subgroup (room) within the group. I don't see a simple way to do this. Perhaps you could use self-joins instead of a proper three-table structure - provided that your report doesn't include additional find criteria to exclude some of the child/grandchild records.

[Aussie John]

FYI My function which I seem to have working, sums the values in the list. (with the purpose to calculate the average). I wasn't able to get an average via this though as when I divided by VC it got caught up in the recursive and gave an incorrect result.

Let([

VC=ValueCount(F);

V=GetValue (F ; i);

i=i+1];

(V+Case(i<=VC; thelist(F;i))

)

I think I will give up. At least I understand recursive functions now!!!

thanks for your time

[Aussie John]

Hmm - I think I have it working. I am not sure it is worth the effort.

chart test.fp7 2.zip

[comment]

I am not sure your charts are showing the correct average values.

[Aussie John]

You are right (as usual). The values are showing sorted low to high. But I have a solution.

If I change the frequency list 'value list' to show a second value, select the Nominal RT (x axis) and sort by the second value it does seem to work. (I opened it in v12 which shows values and is easier to check).