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: 
Anonymous
Not applicable

Concatenate year and month field into date and join mastercalendar

Hi there,

I hope you can help me with the following problem.

From a data source I load a month field and a year field. By using the makedate function I concatenate those two fields to MM-YYYY as Date.

In my script I made a mastercalendar. The date in de mastercalendar (DD-MM-YYYY) is joined on the month and year field (Date) from the datasource, which I had concatenated. QV gets me the following result:

MASTERCALENDAR DATE
MM-YYYY from datasource
01-01-201101-01-2011
02-01-2011-
03-01-2011-
04-01-2011-
05-01-2011-
06-01-2011-
07-01-2011-
08-01-2011-
09-01-2011-
etc.etc.

The make date function gives me automatically the first date of the month. When I select 01-01-2011 from the mastercalendar, I will get the figures from january 2011. That only works for the first of the month. When I for example select 08-01-2011 I would like to have january selected. At this moment that doesn't work. Does anyone know how to perform this?

I hope I am clear in the description of my wishes. Otherwise, ask me.

Thanks for your help.

Regards,

Henco


1 Solution

Accepted Solutions
RSvebeck
Specialist
Specialist

Hi

Try this as your expression:

=sum({1 <Maand = {$(=num(month(only(DateKey)))) }>} Revenue)

Svebeck Consulting AB

View solution in original post

8 Replies
RSvebeck
Specialist
Specialist

Create a textfield containing Year and Month "YYYY-num(month)" in both tables and use the same fieldname. // Robert Svebeck

Svebeck Consulting AB
Anonymous
Not applicable
Author

Hi Robert,

Thanks for your reply.

I think I understand what you mean, but I can only use the date (dd-mm-yyyy) in my mastercalendar to make a join. This is because I use a link table in my qv document.

Regards,

Henco

RSvebeck
Specialist
Specialist

Hmmm, I don't quite understand why you can’t create a new year-month field link. But if you say it will not work, then I believe you. 🙂

But with set analysis, you should be able to extract which month is selected from your date field and then get the sum from your datasource that way.

If you don’t understand what I mean, post a document here with example data and I will try to assist.

//Robert Svebeck

Svebeck Consulting AB
Anonymous
Not applicable
Author

EDIT:

I tried it, but don't succeed. Could you please help me in the document I attached?

What I would like to see is when I select a date, the right month would show up. Eventhough there is only revenue on the first of the month.

Thanks in advance.


Regards,

Henco

>>>>>>

Ah, thanks Robert. That will work for me!

Regards,

Henco

RSvebeck
Specialist
Specialist

Hi

Try this as your expression:

=sum({1 <Maand = {$(=num(month(only(DateKey)))) }>} Revenue)

Svebeck Consulting AB
Anonymous
Not applicable
Author

Hi Robert,

Thanks for your help, it works the right way.

But still one question. If I don't make a selection on the DateKey, the table will be empty. Can I arrange that, if nothing is selected,  all months will show up? And after making a selection, it will be the selected month?

Regards,

Henco

RSvebeck
Specialist
Specialist

Hi, yes, that would be like this:

=

if(
count(DateKey)=1
,sum({1 <Maand = {$(=num(month(only(DateKey))))}>} Revenue)
,sum(Revenue)
)

//Robert

Svebeck Consulting AB
Anonymous
Not applicable
Author

Thanks Robert! That's it.

Cheers,

Henco