topic Re: Select row based on dates in QlikView

Select row based on dates

Fri, 19 Jul 2013 05:26:30 GMT

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

Re: Select row based on dates

suniljain — Fri, 19 Jul 2013 05:34:29 GMT

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

Re: Select row based on dates

Fri, 19 Jul 2013 05:38:55 GMT

try to use firstsorted value.

Re: Select row based on dates

Fri, 19 Jul 2013 05:39:38 GMT

try this...

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

Re: Select row based on dates

Fri, 19 Jul 2013 05:51:03 GMT

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

Re: Select row based on dates

Fri, 19 Jul 2013 05:51:22 GMT

Sorry, How would I do that?

Re: Select row based on dates

Fri, 19 Jul 2013 05:52:16 GMT

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

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

Re: Select row based on dates

Fri, 19 Jul 2013 05:56:10 GMT

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

Re: Select row based on dates

Fri, 19 Jul 2013 05:57:54 GMT

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

Re: Select row based on dates

er_mohit — Fri, 19 Jul 2013 06:03:26 GMT

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

Re: Select row based on dates

Fri, 19 Jul 2013 06:08:10 GMT

Thanks I tried it as a calculated dimension and expression gives me //error in calculation for some reason.

Re: Select row based on dates

er_mohit — Fri, 19 Jul 2013 06:28:04 GMT

see the file again here i use your item field in dimension side

Re: Select row based on dates

Fri, 19 Jul 2013 06:37:46 GMT

in pivot--

dimension1-[Customer #]

dimension2-[Customer Name]

dimension3-ItemlookupCode

expression1-FirstSortedValue(Discount,-Sale_EndDate)

eapression2-SaleStartDate

expression3-SaleEndDate