Input field to show the no of months or weeks

[Anonymous]

Hello!

I have pivot chart where my table is so long that are in weekly brackets "MonWeek"   from way back 2009 to current.

It looks like this

Material	MonWeek	10/6/2014	10/13/2014	10/20/2014	10/27/2014
A-001		$11,550		$46,800
A-002		$38,500		$175,500
A-003		$19,250
A-004		$53,900	$24,750	$39,600	$34,650
A-005		$14,700	$9,900	$9,900
A-006		$249,900	$252,400	$143,550	$118,800

Since most of my users have different needs, I'd like to create an input field where The users just need to enter  the no of weeks or months to show in the report.  For example, if they want to show the last 52 weeks, they just enter the no and the MonWeek dimension field will show from 10/28/2023 - 10/27/2014.

I'm still a newbie and confuse when creating a variable and I could appreciate if someone give show it ot me step by step.

Tks.

[Gysbert_Wassenaar]

See attached qvw.

[Anonymous]

Hi!  Gysbert,

   Your format is exactly what I need but for some reasons I can't get it work.  I already tried  replacing the formula using below since my max date is 10/27 being the current Monday Week but still no avail.  I have attached my qvw .. hope you can take a look at it.

({<[Transaction Date]={'>=$(=date(weekstart(today())-7*($(vWeeks)-1)))'}>}Sales)

Tks.

[Gysbert_Wassenaar · Accepted solution]

You had a closing square bracket without a matching opening square bracket in your expression:

=date(max(MonWeek])-7*($(vNWeeks)-1))

Once you remove that you'll see results. See attached qvw

[Anonymous]

Gysbert,

It actually worked if I use it to different qvw but it's showing empty with to the actual qvw.  Even if I copy the object to my qvw, it becomes empty....even if they exactly the same formula.   Do you know if there's a need to do the settings in one of the properties to my actual qvw ?

Btw, If I enter the no 5 in input box, its only showing 4 weeks so I take out the -1 so it will give the needed no of weeks.

Tks.

[Gysbert_Wassenaar]

Maybe the default date format in your document differs from the date format of your date field. You can pass a format string as second parameter to the date() function: date(MonWeek, 'M/D/YYYY')