I have only day as an dimension and need months and years

[#1]

Hi!

I'm a rookie and need some help. My data imported to QV consist of days (ddmmyyyy) and I want to split this into months and years. Can anyone tell me how to?

Many thanks in advance.

Jan Petter

[#2 · Accepted solution]

Hi,

You can do in two ways:-

1. generate Year and Month while loading data by using "Year(yourday) as Year" and "Month(yourday) as Month".

2. you can use expression in the listbox in the qvw for Year you can use "Year(yourday)" and for Month you can use "Month(yourday)"

Thanks & Best Regards,

Kuldeep Tak

[andgid]

I've a table called DATES including different "groupings" of the date:

DATES:
Load
 (year(date($(start_date)+(Iterno()-1)))*10000) + (month(date($(start_date)+(Iterno()-1)))*100) + (day(date($(start_date)+(Iterno()-1)))) as DATE_ID,
 date($(start_date)+(Iterno()-1)) AS THE_DATE,
 year(date($(start_date)+(Iterno()-1))) AS THE_YEAR,
 month(date($(start_date)+(Iterno()-1))) AS THE_MONTH,
 weekday(date($(start_date)+(Iterno()-1))) AS THE_DAY,
 week(date($(start_date)+(Iterno()-1))) AS WEEK_OF_YEAR,
 day(date($(start_date)+(Iterno()-1))) AS DAY_OF_MONTH,
 num(month(date($(start_date)+(Iterno()-1)))) AS MONTH_OF_YEAR,
 ceil(num(month(date($(start_date)+(Iterno()-1)))) / 3) AS QUARTER,
 ceil(num(month(date($(start_date)+(Iterno()-1)))) / 4) AS FOUR_MONTH_PERIOD

 Autogenerate 1
While Date($(end_date))>=date($(start_date))+(Iterno()-1);

The date format is yyyymmdd, but as you see it is easy to change in the script.

end_date and start_date are variables read from a database, and the script will create a table with all dates in that interval.

Maybe not the best solution, but it works great for me

Regards,

/Anders

[#4]

If ddmmyyyy is not your date format, you can reformat it into the correct format using the 'date#' function.

Regards,

Gordon

[#5]

Thank you for a quick respond.

I'm sorry but as a rookie with QV I don't know what you mean by "generate..while loading".

Jan Petter

[#6]

Thank you!

I tried this bur got this error message:

[#7]

I have tried to change the script to ddmmyyyy, but I still get the same error. Unfortunately I don't know what to look for.

DATES:
Load
(day(date($(start_date)+(Iterno()-1)))) + (month(date($(start_date)+(Iterno()-1)))*100) + (year(date($(start_date)+(Iterno()-1)))*10000) as DATE_ID,
date($(start_date)+(Iterno()-1)) AS THE_DATE,
weekday(date($(start_date)+(Iterno()-1))) AS THE_DAY,
month(date($(start_date)+(Iterno()-1))) AS THE_MONTH,
year(date($(start_date)+(Iterno()-1))) AS THE_YEAR,
week(date($(start_date)+(Iterno()-1))) AS WEEK_OF_YEAR,
day(date($(start_date)+(Iterno()-1))) AS DAY_OF_MONTH,
num(month(date($(start_date)+(Iterno()-1)))) AS MONTH_OF_YEAR,
ceil(num(month(date($(start_date)+(Iterno()-1)))) / 3) AS QUARTER,
ceil(num(month(date($(start_date)+(Iterno()-1)))) / 4) AS FOUR_MONTH_PERIOD

Autogenerate 1
While Date($(end_date))>=date($(start_date))+(Iterno()-1);

[andgid]

You have to set date-values to the variables end_date and start_date

I read the values from a database, where the start- and end-dates are set for each period I read into the qlikview-file.

[#9]

Thank you very much!

I tried your alternative #2 and it worked very well.

Jan Petter

[#10]

I see. I have to replace "THE_DATE" and so on. The use of expression =Year(the_date) worked well and seems easier.

Thank you very much for your help.

Jan Petter