Max date for Net Brokerage

[#1]

Hello

I am having a problem trying to set a maximum date for the data we have for Net Brokerage. The previous expression worked but since getting Budget data in the dashboard for the whole 2013, I need to get the expression only looking at the maxiumum dates for Net Brokerage data.

I used this expresison (below) but it is only bringing back the maximum Net Brokerage amount (money) rather than max Net Brokerage date. Please help.

=sum( {<date = {"$(=Max(Date,'DD-MMM-YYYY'),'DD-MMM-YYYY'))"} >} Net_Brokerage_USD)

                                 ^                                                             

                    our date field in the data                                     

Many thanks.

John

[sushil353]

try

=sum({<date={'$(=max(date))'}>}Net_Brokerage_USD)

HTH

Sushil

[manideep78]

Try this

Sum({<date={"$(=Max(Date(Date#(date,'DD-MMM-YYYY'),'DD-MMM-YYYY'')))"}>}Net_Brokerage_USD)

Note: The Date format in Date#() function(Highlighted format) and Data in Date column should be same.

Hope this helps you.

[er_mohit]

try this

load field1

MAX(Net_Brokerage_USD) asNet_Brokerage,

FirstSortedValue(date(DATE#(Date,'DD-MM-YYYY'),'DD-MM-YYYY'),- Net_Brokerage_USD) as mAX_Date

resident tablename

group by field1;

here field1 is that  on corresponding which you want to see the max date and corresponding amount

[#5 · Accepted solution]

I used the below code to resolve this issue.

=

date(MakeDate(year(Max({1}date(date#(Net_Brokerage_Date,'DD-MMM-YYYY'),Net_Brokerage_USD))),Month(max({1}date(date#(Net_Brokerage_Date,'DD-MMM-YYYY'),Net_Brokerage_USD

)))),'MMM-YYYY')

By replicating the date in the actual data to Net_Brokerage_Date, I was able to separate the two dates (actual & budget) to get the desired date.