Date Filtering

The first thing to know is that date filtering is always awkward! Some systems refer to periods, others use the US format and Excel of course converts dates to the 1900 or 1904 system.

A frequent questions is how to filter for a fixed period from the date selected by the user. This could be useful for a non-purchase report or a report where the sales data is displayed over a running period e.g the last 6 months. It is possible within Sage Alchemex to create custom run-time parameters but it is not possible to perform calculations within the parameters in the Report Manager i.e. you cannot add/subtract the period in the Comparato, the below would not work to take 90 days from the user selected date.

runtime parameters

With all the various date formats the simplest way is to use the DATEDIFF SQL query. This has the format DATEDIFF(period type, start date, end date) and this will give you the period (days, weeks or years) between two dates then a filter is applied to this inthe Report Manager.

So for the above example if we wanted to view customers who have purchased in the last 90 days where the [StoreSummary].[LastBuyDate] is the date field giving the last purchase date by the customer and @ENDDATE@ is the pass through parameter then the formula is:

DATEADD('d', [StoreSummary].[LastBuyDate], @ENDDATE@)
(Note that in this case an Access database is used and requires 'd' but for most SQL script simply d is sufficient.)

Add this formula and the pass through parameter into the Connector. In this case the SQL expression is referred to as Last90Purchase and the pass through parameter is referred to as ENDDATE.

DATEDIFF

In this case we are looking for suppliers that have purchased within the last 90 days, so for the corresponding report in the Report Manager Filter we add the expression created above with a Less Than or Equal to 90.

Filter

Don't forget to add the pass through parameter ENDDATE in the Parameters tab!

Parameter

The when the report is run the data will only give sales where the customer has purchased in the last 90 days from the date entered by the user.

 

training

tv

On line via the Alchemex website

On site in NSW Sydney Region, the training is taylored to your needs, be it Alchemex or Excel.

 

report writing

Business Reports directly in Excel at the touch of a button using Alchemex.

All the major accounting packages are supported: MYOB, ABM, Sybiz, Sage packages (Handisoft, Accpac etc)

Excel & Alchemex Tips

Alchemex Tips & Tricks
There's often a little gem in the Alchemex Tips & tricks.

Excel Tips & Useful sites
Chandoo and Ozgrid are very useful......

© Orginal Template by Flipside Digital