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?

[sushil353]

yes it is possible use MONTH(),YEAR(), and DAY() function and make new fields....

apply the above function on the Account Open Date field..

i think it would help you

[IAMDV · Accepted solution]

Please use the DATE and TIME expressions as derived columns.

Example:

Month([Account Open Date])

Year([Account Open Date])

MonthName([Account Open Date])

I hope this helps you.

Cheers - DV

[gerhardl]

Hi,

[gerhardl]

Hi,

Where do I create derived columns? Can I do this in qlikview, or do I have to change this in the script, i.e.

Load

Month([Account Open Date]) as ??????

Please explain to me in detail if possible.

Much appreciated.

G

[IAMDV]

Hi,

If you are planning to use it as Time Dimension then please load it in the Load Script. However, you can also use these functions in both Expressions and Load Script.

If you are using in Load Script Alias the column names and use these functions within your table. And if you wanted to use them as expression you can directly use the [Account Open Date] field name with the DATE and TIME Functions.

I hope this helps...

Let me know if you need more.

Cheers - DV

[IAMDV]

I am glad it worked for you. Sorry I could not reply back to your old question 3 days back. I hope someone might have helped you.

Cheers - DV

[gerhardl]

Thanks deepak it worked great - I was just having trouble because my script also contained date format changes, but I got it to work.

Now I have 3 boxes, "account open day", "account open month" and "account open year". When I select 2011 it works perfectly, and my overview sheet will then show me the number selected.

But I would like to have a permanent box on my "overview" sheet, that shows "the number of accounts opened this month". So it must automatically measure from the 1st of the current month, and every day when I update my script with the new data, it must also include the new day's new accounts. And on the first of the month it must again read "0".

Possible? Thanks for all your help.

Regards,

G

[IAMDV]

Very interesting! I am sure this is certainly possible. However, I would recommend you to start a new post. I will work on this one sometime today, meanwhile other users can pour in their ideas.

Just to give some idea... you need to consider two things to get this working.

1. Assocative Query Logic should not influence for this expression or calculation. I mean irrespective of whichever month you select it should still show the Current Month Till Date (C-MTD). You can achieve this by using variable and set analysis.

2. The expression should be based on the Date Field to calculate the C-MTD score. I mean whenever you load the data it should automatically evaluate the expression and add up the new account (number of accounts).

I hope I had not confused you..

But this is certainly possible and I had done this kinda stuff in the past.

Cheers - DV

[gerhardl]

I have (almost) figured it out. I will start a new post if I can't figure it out 100%.

So far I have created a straight table with the following expressions:

For the accounts opened this year - Count ({1<[Account open year] = {2011}>}[Account open date])

For the accounts opened this month - Count ({1<[Account open month] = {Apr}, [Account open year] = {2011}>}[Account open date])

That works beautifully but I will have to change the expression every month. I just need to replace 2011 and April with "current year" or "current month"? Can you help me with that or should I just start a new post?

Thanks