Working with dates

[gerhardl]

Hi,

I have a field (currently displayed as a table box) with dates that accounts were opened. There are about 120,000 accounts.

I would like to show a summary box (or anything) where I can break down the data a bit more. I would for instance like to show how many accounts were opened during the last week/month/year.

Could anyone set me on the right direction? Also, is there anyway to display the 'account opend dates' in broken down list boxes, i.e. one list box showing Years, another showing Months, another showing Days. So that if I click on 2011 I will see all accounts opened in 2011, and the months Jan, Feb, March, April will be shown as possible values in the other box. Is this possible?

[IAMDV]

Excellent! Good progress...

I would recommend using variables in your experssion. I mean define two variables like

vCurrentYear = Max(Year([Account open year]))

vCurrentMonth = Max(Month([Account open year]))

Then please use them in your expressions... something like this..

Count ({1<Month(Date([Account open year])) = {$(=($(vCurrentMonth)))}>}[Account open date])

Count ({1<Year(Date([Account open year])) = {$(=($(vCurrentYear)))}>}[Account open date])

I hope this helps...

Cheers - DV

[IAMDV]

With the above solution you dont have to change the month & year everytime you load the new data. Because your variables evaluate the Current month based on the function and then return a value in your original expression.

Good luck!

Cheers - DV

[gerhardl]

Hey DV,

I tried my hardest to make sense of that - but no luck. Would you mind explaining it to me step by step?

I go to Settings - Variable Overview, then I add one called "vCurrentYear" with definition Max(Year([Account open year]))

And then a second one "vCurrentMonth = Max(Month([Account open year])). Should this not be Account open month??

After that I am lost, I have no idea how those expressions work. When I change my current expressions to the ones you gave me I get "Bad fieldname: Year, Date" at the top of the expression box...

This is my load statement:

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],
[Charge off Status],
DATE(DATE#( [Account status change date], 'DD-MMM-YY')) as [Account status change date], etc.