How to display first and last value ?

[#1]

I am new to qlikeview,  basically i want display first and last (based on user chosen date range )  record from the following rows.

Source

Client	Product	Date	Opening Balance	Addition	Withdrawal	Closing Balance
A	X	01/04/2016	100000	2000	0	102000
A	X	02/04/2016	102000	0	1000	101000
A	X	05/05/2016	101000	0	0	101000
A	X	12/08/2016	101000	3000	0	104000
A	Y	01/04/2016	2000	0	0	2000
A	Y	05/05/2016	2000	100	0	2100
A	Y	12/08/2016	2100	0	50	2050
B	X	01/04/2016	3000	200	0	3200
B	X	12/08/2016	3200	0	50	3150

filter : From 01/04/2016 to 12/08/2016

Output

Client	Product	Date	Opening Balance	Addition	Withdrawal	Closing Balance
A	X	01/04/2016	100000	5000	1000	104000
A	Y	01/04/2016	2000	100	50	2050
B	X	01/04/2016	3000	200	50	3150

[#2]

Hi,

Please check the attached file

[Anonymous]

You want for First Value of Date only??

Have you checked FirstValue() & LastValue()  or FirstsortedValue() function in qlik?

First and Last value

[varshavig12]

addition:

aggr(sum({<Date=>}Addition),Client,Product)

Withdrawal:

aggr(sum({<Date=>}Withdrawal),Client,Product)

[Frank_Hartmann]

Have a look at attached qvw.

Dimensions:

-client

-Product

-Date

Expressions:

-Opening Balance: =aggr(min([Opening Balance]),Product,Client)

-Addition: aggr(sum({<Date=>}Addition),Client,Product)

-Withdrawal: aggr(sum({<Date=>}Withdrawal),Client,Product)

- closing Balance: =[Opening Balance]+aggr(sum({<Date=>}Addition),Client,Product)-aggr(sum({<Date=>}Withdrawal),Client,Product)

Hope that helps

[susovan]

Hi Rock,

PFA

[Digvijay_Singh]

Try this -

[qliksus]

Please check if this ok for you

Opening Balance : sum({<Date={'$(=date(min({1}Date),'DD/MM/YYYY'))'}>}[Opening Balance])

Addition : sum({<Date={'$(=date(min({1}Date),'DD/MM/YYYY'))'}>} aggr(  sum( TOTAL<Client,Product>  Addition) ,Client,Product) )

Withdrawl : sum({<Date={'$(=date(min({1}Date),'DD/MM/YYYY'))'}>} aggr( sum(TOTAL<Client,Product> Withdrawal) ,Client,Product) )

Closing Balance: sum({<Date={'$(=date(min({1}Date),'DD/MM/YYYY'))'}>} TOTAL<Client,Product> aggr( sum({<Date={'$(=date(max({1}Date),'DD/MM/YYYY'))'}>}[Closing Balance]) ,Client,Product) )

[Digvijay_Singh]

Implemented Start Date and End Date selection logic -

[#10]

ya, thank you. now its always returning max and min date value, but it should return  based on the date range. Example:- if i choose from date: 01/04/2016 To date: 01/04/2016. i should get following output.

Source

Client	Product	Date	Opening Balance	Addition	Withdrawal	Closing Balance
A	Y	01/04/2016	2000	0	0	2000
A	Y	05/05/2016	2000	100	0	2100
A	Y	12/08/2016	2100	0	50	2050
A	X	01/04/2016	100000	2000	0	102000
A	X	02/04/2016	102000	0	1000	101000
A	X	05/05/2016	101000	0	0	101000
A	X	12/08/2016	101000	3000	0	104000
B	X	01/04/2016	3000	200	0	3200
B	X	12/08/2016	3200	0	50	3150

Output

Client	Product	Date	Opening Balance	Addition	Withdrawal	Closing Balance
A	X	01/04/2016	100000	2000	0	10200
A	Y	01/04/2016	2000	0	0	2000
B	X	01/04/2016	3000	200	0	3200