Calculating store spend between certain dates

Calculating store spend between certain dates

Note: To follow this tip, you must have a contains exact link between the store and customer data. See Linking Schemas with a Common Column for more information on how to do this.

• Open a browse on your store schema

• Right click anywhere in the browse and click Add Column…

• Go through the blue link to customers, through Sum and finally double click on the master table

• Right click on the column that holds the dates and select Paste Count

An expression will appear in the Expression box and you will notice that <value> is highlighted. Replace this with the dates you want to calculate spend for, following the format below.

BRANCHCUSTOMERS:( INT( MASTER.DATE > cdate('31-12-2009' ) and MASTER.DATE < cdate('1-1-2011' ) ) )

• This shows everything in the year 2010.

• If you wanted the dates to be between, say January 2011 and May 2011, then you would change it to

>cdate('31-12-2010') and <cdate('1-6-2011')

Finally, we need to multiply it by spend. Put the cursor between the last two brackets of the expression, type * then paste the spend column.

It should look like this:

BRANCH_CUSTOMERS:( INT( MASTER.DATE > cdate('31-12-2009') and MASTER.DATE < cdate('1-1-2010')) * BRANCH_CUSTOMERS:(MASTER.SPEND))

Finally, remove the part that is in yellow, as it is not needed.