Limit dimension by date value

[#1]

Hi, I have an input box where the user inputs a date in  MM/YYYY format.  I would like to put a line of set analysis in my dimension limit my graph to the input box date minus 12 months.  Any suggestions?  thanks, Jim

[Anonymous]

Input box means a variable.  Assuming, it has name vDateLimit.  Assuming also that your dimension is associated with the field Month, in the same format MM/YYYY.
First, create another variable to get to one year back:
LYDateLimit = date(addyears(date#(vDateLimit, 'MM/YYYY'),-1),'MM/YYYY')
So, if your enter vDateLimit = 01/2011, then your LYDateLimit will be 01/2010.  The set syntax is:
{<Month={'$(LYDateLimit)'}>}

Hope it will help you to figure out the rest if I missed anything.

Regards,
Michael

[#3]

Thanks... I'm almost there, so once I've created my variabl VDateLimit, i put the set analysis into the dimension....

{<Month={'$(VDateLimit)'}>}  and I tried replacing "Month" with my month field.  It didn't work.  My month field is MM/YYYY.

[#4]

Thanks... I'm almost there, so once I've created my variabl VDateLimit, i put the set analysis into the dimension....

{<Month={'$(VDateLimit)'}>}  and I tried replacing "Month" with my month field.  It didn't work.  My month field is MM/YYYY.

[Anonymous]

Jim,

I can't tell what you miss.  See the attached simple example.  See what variables are there, and the expression in the bottom text box.

There could be nuances in your situation which I don't know about, so look at the attachment as an example rather than a solution.

Regards,

Michael

[#6]

Thanks Michael, this is great.  How does the last text object box know to subtract 12 months? and where is the LYDateLimit variable created?  Jim

[Anonymous]

I created it in Variables Overview

[nagaiank]

If you change your dimension from

RollMonthDateAL

to

=Date(If(RollMonthDateAL > MonthStart(Today(),-12), RollMonthDateAL, Null()),'MM/YYYY')

You can limit the number of bars to 12

[Anonymous · Accepted solution]

Jim,

I usually don't download attachments, especialy of that size.

Judging by the response from Krishna, your requirement is not about the month one year prior to the "input box month", but 12 months prior.  If this is correct, Krishna's suggestion makes sense.  Except, the "Today()" in his expression should be replaced with the first date of the month which is in the input box.

Regards,

Michael

[#10]

Thanks Michael and Krishnamoorthy!!   This works and I will use both the "Today()" and the input box method.  Jim