topic Re: Limit dimension by date value in QlikView

Limit dimension by date value

Wed, 11 Jul 2012 21:39:41 GMT

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

Re: Limit dimension by date value

Anonymous — Thu, 12 Jul 2012 00:36:50 GMT

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

Re: Limit dimension by date value

Wed, 18 Jul 2012 15:46:03 GMT

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.

Re: Limit dimension by date value

Wed, 18 Jul 2012 15:46:19 GMT

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.

Re: Limit dimension by date value

Anonymous — Wed, 18 Jul 2012 23:33:43 GMT

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

Re: Limit dimension by date value

Thu, 19 Jul 2012 13:58:56 GMT

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

Re: Limit dimension by date value

Anonymous — Thu, 19 Jul 2012 16:41:24 GMT

I created it in Variables Overview

Re: Limit dimension by date value

nagaiank — Sun, 22 Jul 2012 14:44:24 GMT

If you change your dimension from

RollMonthDateAL

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

You can limit the number of bars to 12

Re: Limit dimension by date value

Anonymous — Sun, 22 Jul 2012 22:35:16 GMT

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

Re: Limit dimension by date value

Mon, 23 Jul 2012 12:52:25 GMT

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

Re: Limit dimension by date value

Fri, 03 Aug 2012 13:47:33 GMT

Hi Michael, can I send a question on a QV issue?

Jim Francisco

M&T Bank Treasury - ALM group

Understanding What's Important

(716)842-5684

Re: Limit dimension by date value

Fri, 03 Aug 2012 19:15:02 GMT

Michael, the if statement in my dimension expression box works great to limit my dimension periods to 18 mths.

=Date(If(PeriodDate > MonthStart(Today().-18,PeriodDate, Null()),'MM/YYYY')

Now my chart shows the dates as numbers. Can I switch them back to the MM/YYYY format? The dates are set in the load statement to load as MM/YYYY. Thanks, Jim

Re: Limit dimension by date value

Fri, 10 Aug 2012 20:48:25 GMT

Hi Krishnamoorthy, I've been using the dimension expression you provided in my Pivot Table.

=Date(If(PeriodDate > MonthsStart(Today)-18),PeriodDate,Null()),'MM/YYYY')

The only problem is that is returns a collumn with a Null date, and sums up the data that doesn't match the date criteria.

3/2012 4/2012 5/2012 6/2012 7/2012 -

$1000 $2000 $3000 $4000 $5000 $6000

Re: Limit dimension by date value

Mon, 13 Aug 2012 14:31:50 GMT

got it. Dimension, Settings, "supress when value is null"