Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Darumaka
Contributor
Contributor

YYYYMM to date variable

I have a numeric variable that contains year and month in the format YYYYMM and I have a few questions.

How do I create a date variable from this variable (day can be 1)?
What is the best way to create  another variable with the format YYYY-MM? (need this for presentation)

In the end I want to have a way to make a column that calculates the difference compared to same month previous year, will the date variable help me achieve this?

Grateful for help and also if someone could redirect me to some documentation on this topic.

1 Solution

Accepted Solutions
rubenmarin

Hi, you can create a date on day 1 with makedate():

Makedate(Left(DateField,4), Mid(DateField, 5, 2))

To give format you can use Date() function:

Date(Makedate(Left(DateField,4), Mid(DateField, 5, 2)), 'YYYY-MM') -> Note that the date will still have the day 1, no matter if it's shown or not.

View solution in original post

10 Replies
rubenmarin

Hi, you can create a date on day 1 with makedate():

Makedate(Left(DateField,4), Mid(DateField, 5, 2))

To give format you can use Date() function:

Date(Makedate(Left(DateField,4), Mid(DateField, 5, 2)), 'YYYY-MM') -> Note that the date will still have the day 1, no matter if it's shown or not.

Darumaka
Contributor
Contributor
Author

Thank you, that worked out perfectly!

But if you have a date variable and you want to present it in different ways for different purposes, do you have to create different variables that does that? Or can you have just one date variable and sometimes use it's year part, sometimes the form YYYYMM and sometimes YYYY-MM?

Also grateful if you or someone else knows where I can find documentation of how to use the datevariable to calcluate differenses with same month previous year.

rubenmarin

Hi, it's a best practice to have a calendar table that creates all fields that can derive from a date, I usally have the linked date as a number, another field with presentable date format, and maybe other fieldss with other formats.

 

LOAD 
	 IdFecha			as _IdFecha,
	 Date(IdFecha)			as Fecha,
	 Month(IdFecha) 		as Mes,
	 Num(Month(IdFecha))		as MesNum,
	 Year(IdFecha) 			as Año,
	 Date(IdFecha, 'YYYY/MM')	as Año_Mes
;
LOAD 
	 AddMonths(MinFecha, IterNo()-1) as IdFecha
While AddMonths(MinFecha, IterNo()-1) <= MaxFecha
;
LOAD 
	 MonthStart(Min(FieldValue('_IdFecha', RecNo()))) 	as MinFecha,
	 MonthStart(Max(FieldValue('_IdFecha', RecNo()))) 	as MaxFecha
AutoGenerate FieldValueCount('_IdFecha');

 

 

Darumaka
Contributor
Contributor
Author

Ok, so I have created a calender containing monthvariables in two different versions, year and then a variable containing the same month previous year.

Now, how do I use this variable in my expression? My current expression looks like this:

count( {$<filterVariabel={1}>} distinct IDvariable)

In the dimensions I have the current month but for the current month I also want to show the value for the same month previous year.

rubenmarin

Hi, having a master caledar you can use set analysis with the Year field to filter data from one year or another, ie:

Max Year: Sum({<Year={$(=Max(Year))}>} Sales)

Last Year: Sum({<Year={$(=Max(Year)-1)}>} Sales)

 

Darumaka
Contributor
Contributor
Author

Hmm, but then I don't use the variable for same month previous year. Modifying my expression like you suggested doesn't give the desired results but these set expressions are really foreign to me.

count( {$<filterVariable={1}>} {<Year={$(=Max(Year)+1)}>} distinct IDvariable)

Thinking further, if i have the (current) month variable in my listbox, will it then even be possible to return the results for another month on the same row? I can't see attachments in this forum because of organizational settings so I have inserted a table to show the result I am looking for.

Current monthValue for current monthSame  month previous yearValue for same month previous year
202004200201904100
201904100201804N/A
rubenmarin

Having monthyear as a dimension makes it a bit more complicated, in particular if dimension values can have diffrent years.

One option is using an asof table: https://community.qlik.com/t5/Qlik-Design-Blog/The-As-Of-Table/ba-p/1466130

The other option is to have the previous year value precalculated in script,  but this can vary depending of the script.

Darumaka
Contributor
Contributor
Author

Thank you for trying to help me. I think that might be a bit advanced for me at this point. The easiest way for me is to simply create a new table comparing this year with previous year, month by month (using the monthame instead).

rubenmarin

With this script:

LOAD Num(date#(Date, 'YYYYMMDD')) as _IdFecha, Value INLINE [
    Date, Value
    20200401, 200
    20200301, 150
    20190301, 90
    20190401, 100
];

Calendar:
LOAD 
	 IdFecha			as _IdFecha,
	 Date(IdFecha)			as Date,
	 Month(IdFecha) 		as Month,
	 Num(Month(IdFecha))		as MonthNum,
	 Year(IdFecha) 			as Year,
	 Date(IdFecha, 'YYYY/MM')	as Year_Month
;
LOAD 
	 AddMonths(MinFecha, IterNo()-1) as IdFecha
While AddMonths(MinFecha, IterNo()-1) <= MaxFecha
;
LOAD 
	 MonthStart(Min(FieldValue('_IdFecha', RecNo()))) 	as MinFecha,
	 MonthStart(Max(FieldValue('_IdFecha', RecNo()))) 	as MaxFecha
AutoGenerate FieldValueCount('_IdFecha');

You can create a table using Year and Month as dimensions and this two expressions:

Max Year: Sum({<Year={$(=Max(Year))}>} Value)

Previous Year: If(Year=Max(TOTAL Year), Sum(TOTAL <Month> {<Year={$(=Max(Year)-1)}>} Value))