Qlik Community

Ask a Question

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Select row based on dates

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

Current

Customer #Customer NameItemlookupCodeDiscount
SaleStartDateSaleEndDate
1010AA93615%1/07/201130/06/2012
1010AA93615%1/07/201231/12/2012
1010AA93613%1/1/201331/12/2013
1111CC691219%1/07/201131/12/2012
1111CC691212%1/1/201301/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 NameItemlookupCodeDiscount
SaleStartDateSaleEndDate
1010AA93613%1/1/201331/12/2013
1111cc691212%1/01/201301/03/2013
12 Replies
Master
Master

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

Not applicable

try to use firstsorted value.

Not applicable

try this...

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

Not applicable

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

Not applicable

Sorry, How would I do that?

Not applicable

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

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

Not applicable

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

Not applicable

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 NameItemlookupCodeDiscountmax_Sale_StartDatemax_Sale_EndDate
1010AA93613%1/1/201312/31/2013
1111CC691212%1/1/20133/1/2013


Master II
Master II

See the attached file

and output like this

Customer #Customer NameItemDiscountSale Start DateSale End Date
1010AA93613%01/01/201331/12/2013
1111CC691212%01/01/201301/03/2013