In order to have date fields broken down into 3 different fields each (day, month and year), I did the following:
LOAD [Product Name], [ID number], [Account no], [Card no], DATE(DATE#( [Account open date], 'DD-MMM-YY')) as [Account open date], month(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open month], year(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open year], day(DATE(DATE#( [Account open date], 'DD-MMM-YY'))) as [Account open day], [Account status code],
So now I have a list box for each in which I can make my selections, e.g. accounts opened in 2011 and April, or whatever.
On my overview sheet I would like to add the amount of customers who opened an account in the year, and in the month. At the moment I have this in a straight table with the following expressions:
Accounts opened this year - Count ({1<[Account open year] = {2011}>}[Account open date])
Accounts opened this month - Count ({1<[Account open month] = {Apr}, [Account open year] = {2011}>}[Account open date])
This works fine, but I would obviously have to update the expressions every month. I would like to change this so it automatically looks at "current month" and "current year".
What is the best way to do this?
I've created two variables, but I have no idea how to use them in expressions. They are:
Max(Year([Account open year])) and Max(Month([Account open year])) named vCurrentYear and vCurrentMonth.
Can someone please help me with these expressions, I have NO idea how variables work??
