Variable - Current Month

[gerhardl]

Hi,

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??

Thanks,

G

[IAMDV]

Please can you load the QV document with some sample data. I can certainly help you 🙂

Cheers - DV

[gerhardl]

Hi DV,

I've attached a sample. Hope it is okay.

Please note that I had to change my "month" expression in the sample file. Because I exported the sample data to excel, the month formatting is different.

So in the attached file the formula is Count ({1<[Account open month] = {2}, [Account open year] = {2011}>}[Account open date])

In my original file it is Count ({1<[Account open month] = {Apr}, [Account open year] = {2011}>}[Account open date])

Much appreciated,

G

[IAMDV]

Here you go...Please follow the below steps. Also if you wanted the QV document please send me a personal message from profile providing your email address and I'll email you the QV document.. Sorry! I can't upload QV documents from my system.

Below expressions & variables work only if you dont change the names. I am sure you are aware of this fact.

Steps:

1. Press ALT + CTRL + V (All together) to access the Variable Overiew window

2. Type the Variable Name for

Current Year : vCurrentYear

and

Current Month = vCurrentMonth

3. Paste this value after selecting the vCurrentYear variable

Max({1} [Account open year])

This selects the max year across all the years in the data.

4. Paste the below expression after selecting the vCurrentMonth variable

Max({1<[Account open year] = {$(=$(vCurrentYear))}>} [Account open month])

5. Now use the below expression to calculate the number of accounts opened this year

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

(You have to be extremely cautious with the brackets)

6.Now use the next expression to calculate the number of accounts opened current year and current month (checking two conditions)

=COUNT({1<[Account open year]={$(=($(vCurrentYear)))}, [Account open month]={$(=($(vCurrentMonth)))}>} [Account open date])

This should return 98 & 14 respectively and it seems to be accurate.

I am also attaching an image to help you...

Cheers - DV

[gerhardl]

Hi DV,

Thanks for the very detailed explanation. Unfortunately there is still a problem. This works 100% in the sample file I gave you, but in my own document, the current month calculation returns a "0" value.

The only reason for this (I think) can be that the month formats are different in the documents. In the sample file they are month 1,2,3,4 etc. In my document they are 'MMM", so they will be Jan, Feb, Mar, Apr, etc. Could this be the problem?

Thanks,

G

[IAMDV]

Hi Again,

I am sure that is the problem. Because variable is returning an integer value in the sample file and it is returning string as month in the actual file.

Try the below steps..

1. Change the vCurrentMonth to

Max({1<[Account open year] = {$(=$(vCurrentYear))}>} MonthNumber([Account open month]))

(Only difference is that I am rounding the [Account open month] with MonthNumber function)

2. Change the Month Calculation expression to

=COUNT({1<[Account open year]={$(=($(vCurrentYear)))}, MonthNumber([Account open month])={$(=($(vCurrentMonth)))}>} [Account open date])

I am sure this should work for you... If you still have issues then post the sample file with the Month Name format (MMM).

[gerhardl]

Hi DV,

Still not working - can you have a look and see if you can pick up an error in the expression? It now just returns the same number as for the year.

Thank you,

G

[IAMDV]

Sorry! the image is too small to see the expression. Preferably please can you post the QV document with some sample data and highlighting the issue?

That really speeds up the process to help!

[gerhardl]

I'm afraid I'm a bit busy at the moment so creating a data sample now is not possible - plus for all of the trouble I can just change my expression every month. I just like things to be right, you know..?

Is there no date function that can be used that looks at the date on the system clock? Anyway, thanks for your trouble - I've attached the picture again and hopefully it is big enough to see. The expression seems to have a problem with "MonthNumber" which is displayed in red and the rest of the statement has no colors. - but if this doesn't help, I'll get back to this issue some other day.

[IAMDV · Accepted solution]

Okay, try this one to see if it works.

1. Change the vCurrentMonth variable to this one...

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

earlier we had used...

(Max({1<[Account open year] = {$(=$(vCurrentYear))}>} [Account open month]))

2. Add a new variable called vCurrentMonthName converting to MonthName

vCurrentMonthName : Month($(vCurrentMonth))

3. Change your month expression with new variable

=COUNT({1<[Account open year]={$(=($(vCurrentYear)))}, [Account open month]={$(=($(vCurrentMonthName)))}>} [Account open date])

Let me know if this helps... I hope you understood the idea 🙂

Good luck!