Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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-2011 | 01-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
Hi
Try this as your expression:
=sum({1 <Maand = {$(=num(month(only(DateKey)))) }>} Revenue)
Create a textfield containing Year and Month "YYYY-num(month)" in both tables and use the same fieldname. // Robert Svebeck
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
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
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
Hi
Try this as your expression:
=sum({1 <Maand = {$(=num(month(only(DateKey)))) }>} Revenue)
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
Hi, yes, that would be like this:
=
if(
count(DateKey)=1
,sum({1 <Maand = {$(=num(month(only(DateKey))))}>} Revenue)
,sum(Revenue)
)
//Robert
Thanks Robert! That's it.
Cheers,
Henco