Top 5 Items By Region, By Product, and < 30 days

[veldetta]

I created a straight table to show the top 5 items by region and by product, however, I'm having difficulties showing the same data but for items with a date of less than 30 days.

Currently I have these as dimensions:

=Aggr(if(Rank(Sum({<[Open/Closed]={'Open Items'}, Filtered={1}>}Amount))<=5,[Item Number]),Region, [Product], [Item Number])

=if([Open/Closed]='Open Items','Open')

and I have this as an expression:

Aggr(Rank(sum({<[Item Number]-={'Dummy'}, [Projected Closing Date]={"<=$(=(date(vLessThan30)))"}>}[Month End Amount])),Region,[Product],Customer, [Item Number])

(vLessThan30 = Today()+30)

which includes the set analysis for less than 30 days but it doesn't seem to working.  Need help figuring this out.

[Anonymous]

you're looking into the future, perhaps try:

vLessThan30 = Today()-30

and then

Aggr(Rank(sum({<[Item Number]-={'Dummy'}, [Projected Closing Date]={">=$(=(date(vLessThan30)))"}>}[Month End Amount])),Region,[Product],Customer, [Item Number])

[veldetta]

Yes, I want to look into the future.  So if I ran the report today, I would want to see all items < 10/1/2017.

[Anonymous · Accepted solution]

ok , now I understand, perhaps:

Aggr(Rank(sum({<[Item Number]-={'Dummy'}, [Projected Closing Date]={"<=$(=(date($(vLessThan30))))"}>}[Month End Amount])),Region,[Product],Customer, [Item Number])0

(vLessThan30 = Today()+30)

[veldetta]

That works!  Thanks!!!