Select row based on dates

[#1]

Hi I have a sample data below of what I'm currenty viewing & what I would like to view.

Current

Customer #	Customer Name	ItemlookupCode	Discount	SaleStartDate	SaleEndDate
1010	AA	9361	5%	1/07/2011	30/06/2012
1010	AA	9361	5%	1/07/2012	31/12/2012
1010	AA	9361	3%	1/1/2013	31/12/2013
1111	CC	6912	19%	1/07/2011	31/12/2012
1111	CC	6912	12%	1/1/2013	01/03/2013

This is what I need, basically the latest saleenddate is all I'm interested in to see what is the latest sale for a Item.

Not sure what type of expression will produce this output for me

Customer #	Customer Name	ItemlookupCode	Discount	SaleStartDate	SaleEndDate
1010	AA	9361	3%	1/1/2013	31/12/2013
1111	cc	6912	12%	1/01/2013	01/03/2013

[suniljain]

please use dimension limit avaialble in QV 11+ .  it's in built feature .

[#3]

try to use firstsorted value.

[#4]

try this...

Num(Sum({<SalesStartDate={$(=Max(Year))} >}Discount)'###.%')

[#5]

Thanks, but I'm unfortunately I'm using v10

[#6]

Sorry, How would I do that?

[#7]

Ok, so should this be a calculated dimension or expression?

also '###.%' what is that supposed to represent?

[#8]

as ur measure is discount and discount is showing (%)..

syntax...num( expression [ , format-code [ , decimal-sep [ , thousands-sep ] ] ] )

The num function formats the expression numerically according to the string
given as format-code. Decimal separator
and thousands separator can be set as third and fourth parameters. If the
parameters 2-4 are omitted, the number format set in the operating system is
used.

.. '###.%'    this format will help u data with %symbol in ur chart

[#9]

see attachement

or see this code

in script--

DATA:

LOAD

[Customer #],   

[Customer Name],

ItemlookupCode,

Discount,

date(date#(SaleStartDate,'DD/MM/YYYY'),'DD/MM/YYYY') AS Sale_StartDate ,

date(date#(SaleEndDate,'DD/MM/YYYY'),'DD/MM/YYYY') AS Sale_EndDate;

LOAD * Inline [

Customer #,    Customer Name,    ItemlookupCode,    Discount,SaleStartDate,    SaleEndDate

1010,    AA,    9361,    5%,    1/07/2011,    30/06/2012

1010,    AA,    9361,    5%,    1/07/2012,    31/12/2012

1010,    AA,    9361,    3%,    1/1/2013,    31/12/2013

1111,    CC,    6912,    19%,    1/07/2011,    31/12/2012

1111,    CC,    6912,    12%,    1/1/2013,    01/03/2013

];

OUTPUT:

LOAD

[Customer #],   

[Customer Name],

ItemlookupCode,

date(max(Sale_StartDate)) as max_Sale_StartDate,

date(max(Sale_EndDate)) as max_Sale_EndDate

Resident DATA

Group By [Customer #],[Customer Name],ItemlookupCode;

***********************************************

in pivot--

dimension1- [Customer #],   

dimension2- [Customer Name],

dimension3----ItemlookupCode,

expression 1- FirstSortedValue(Discount,-Sale_EndDate)

expression2--max_Sale_StartDate

expression3--max_Sale_EndDate

then output like this

Customer #	Customer Name	ItemlookupCode	Discount	max_Sale_StartDate	max_Sale_EndDate
1010	AA	9361	3%	1/1/2013	12/31/2013
1111	CC	6912	12%	1/1/2013	3/1/2013

[er_mohit]

See the attached file

and output like this

Customer #	Customer Name	Item	Discount	Sale Start Date	Sale End Date
1010	AA	9361	3%	01/01/2013	31/12/2013
1111	CC	6912	12%	01/01/2013	01/03/2013