Get max date in expression based on dimension

[#1]

Hello,

I am using pivot table to show the inventory data.

For 1 material no, there are different batch dates.

So as on any selected date in calender, I want to consider only max batch date on or before calender date.

for eg:

if Jan 2015 is my calender date, and material no is 11106 and there are batch dates in June 2014, Dec 2014 and March 2015.

So as on Jan 2015, dec 2014 is the max batch date.

So how to write the expression using set analysis for this?

[hariprasadqv]

Hi,

Could you please post the sample data!

[sunny_talwar]

Like hariprasadqv‌ said it would be easier to give you a better suggestion if we have a sample to look at, but if you just need general guidance, I would suggest creating flags in the script where the data exits and then use that flag in your expression. Once you select a date, the max expression with the flag should get you to what you are looking for.

HTH

Best,

Sunny

[#4]

Hello,

Sample data 

material no	BatchDate	Amount
11106	08/22/2013	223.67
11106	09/26/2013	223.67
11106	12/31/2013	223.67
11106	02/28/2014	223.67
11106	03/14/2014	223.67
11106	04/23/2014	223.67
11106	08/25/2014	223.67
11106	08/30/2014	223.67
11106	09/02/2014	223.67
11106	09/10/2014	223.67
11106	09/19/2014	223.67
11106	12/31/2014	223.67

So from the above list, it should show only 12/31/2014.

For this currently, I have expression as

num(sum({$<Year = , Month =, Day =, PostingDate = {'>=$(=Date(MonthStart(min(CalenderDate)))) <=$(=Date(max(CalenderDate)))'}   >} qty),'#,###.000')

In this expression, I want to add 1 more condition for batch date.

[#5]

In General  You Could Use MAX in the set analysis and  Nullify the Month Selection In the Expression so That always max of month will show even if u select the any month..

But if u provide sample data we can sort out the exact solution ..

[sunny_talwar]

Have a look at the attached qvw. Let me know if this helps.

Best,

Sunny

[#7]

Hello hariprasadqv,

I already have a set analysis expression as given in earlier reply.

I have to add 1 more condition in the same.

I will explain again.

If I select Jan 2015, i should get DEc 2014 as the batch date.

If I select Oct 2014, I should get 09/19/2014 as the batch date.

So in this way, as per the selection in the calender, batch date will change.

[#8]

Try This

=Num(sum({$<Year = , Month =,Date={"$(=Max(Date))"}>}Amount ),'#,###.000')

[hariprasadqv]

Subbarja approach will resolve your issue.

[#10]

so you mean, I should write my expression as below:

num(sum({$<Year = , Month =, Day =, PostingDate = {'>=$(=Date(MonthStart(min(CalenderDate)))) <=$(=Date(max(CalenderDate)))'} , BatchDate = {'$(=Date(=Max(BatchDate)))'}  >} qty),'#,###.000') ?

I tried with this, but it is not giving any output.