Posted: 2016-05-09 12:37 |
It might be useful to compare how much a company has spent with you this year, compared to the same point in the year last year. For example, Company A has spent £1000 so far this year until today 9th March, how much did they spend between the 1st of January and 9th March last year? It is possible to start this report from a Template report, 'Template - Sales trends by Client', and amend the 'Last Month' column and '2 Months ago' column to be 'This Year' and 'Last Year' as below: To see the sales so far this year, amend the 'Last Month' column formula on the Details tab to: IF(YEAR(document_date)=YEAR(NOW()), order_line_items.home_currency_gross_value,0) Then simply amend the name of the 'Last Month' column on the Summary tab to 'This Year'. For last year's Sales to date, amend the '2 Months ago' column formula on the Details tab to: IF(document_date BETWEEN MAKEDATE(YEAR(NOW())-1, 1) AND MAKEDATE(YEAR(NOW())-1, DAYOFYEAR(NOW())), order_line_items.home_currency_gross_value,0) Then amend the name of the '2 Months ago' column on the Summary tab to 'Last Year'. The remaining columns on the Summary View will correctly compare the difference between Last Year and This Year. |