Division operator in custom report formulas

[ian_roberts]

Continuing the discussion from Can't get divide function to work in report formula:

@DBS

Hi,
I’m a newbie to quickfile so aplogise in advance if missing something simple. I have searched forum and found no results.
i am testing custom reports and cannot get divide function to work for example if I take nominal code 4000 and try and divide it by say nominal code 5000 (see below for what i have tried) i only get the figure for nominal code 4000 in my report

sales 4000 where nominal account 4000 value is 25

sales1 4000/5000 where nominal 4000 vale is 25 and nominal 5000 value is 10

Expected result in report would be sales 23 sales1 2.3
what I actually get is sales 23 sales1 23
To make matters worse I cannot get the symbol / to appear in the formula i have to copy the traditional sign for divide line with dot above and below to even get sales1 to print but it obviously ignores it as it only prints value for nom code 4000

No idea what I’m doing wrong but cannot get divide sign / to even type into the formula.

add to this another question how would i calculate say a purchases as a percentage of sales using formula

purchases divided by sales times 100 normally I would expect this to be entered as follows

(4000/5000)*100 where 400 is the sales nominal and 5000 is the purchases
but as I can@t insert / I don’t know where to start. I have tried on both firefox and chrome same results
any help appreciated

I’ve just run up against this issue too. I’m trying to come up with a report that will show me my sales VAT collected as a proportion of total gross turnover, so I can track whether at any point it would be worth considering moving onto the flat rate scheme. So the formula would be

2200 / (2200 + 4000:4999)

This does actually work correctly, but I had to prepare the formula in a text editor and then copy-paste it into the formula box in order to get around the form field validator which does not allow the / character to be typed into the formula box.

The “examples” pop up on the custom report creation page suggests that division is a valid operator, so would you consider adding / as a valid character (i.e. add 47 as an option in the keypress handler)?

[Glenn]

That’s a very useful formula

Let me try this on a test account and see if we can relax the validation.

EDIT:

@ian_roberts

The forward slash character has now been white-listed. Although it won’t go live until the next release.

I’m wondering given what you’re trying to do here if it’s possible for us to just add an additional button on the return itself to “Simulate Flat Rate VAT”. I can’t imagine this being hard, but we’d need to take a closer look.

[ian_roberts]

My formula is a bit of a hack, and it will blow up for date ranges that include a VAT return submission, but it’s a useful rough estimate if you look at the bar chart view on week granularity and ignore any weeks that include a VAT return journal. Ideally you’d want to consider just the (many small) credit entries on 2200 created by sales invoices and not count the (occasional large) debits created by the VAT return.