topic Max date for Net Brokerage in QlikView

Max date for Net Brokerage

Mon, 25 Feb 2013 11:40:40 GMT

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

Re: Max date for Net Brokerage

sushil353 — Mon, 25 Feb 2013 11:43:10 GMT

try

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

HTH

Sushil

Re: Max date for Net Brokerage

manideep78 — Mon, 25 Feb 2013 11:53:43 GMT

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.

Re: Max date for Net Brokerage

er_mohit — Mon, 25 Feb 2013 12:03:40 GMT

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

Re: Max date for Net Brokerage

Mon, 15 Apr 2013 13:48:05 GMT

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.