topic Re: How to display first and last value ? in QlikView

How to display first and last value ?

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: How to display first and last value ?

Mon, 20 Jun 2016 07:03:34 GMT

Hi,

Please check the attached file

Re: How to display first and last value ?

Anonymous — Mon, 20 Jun 2016 07:09:27 GMT

You want for First Value of Date only??

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

First and Last value

Re: How to display first and last value ?

varshavig12 — Mon, 20 Jun 2016 07:17:10 GMT

addition:

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

Withdrawal:

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

Re: How to display first and last value ?

Frank_Hartmann — Mon, 20 Jun 2016 08:03:29 GMT

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

Re: How to display first and last value ?

susovan — Mon, 20 Jun 2016 09:09:37 GMT

Hi Rock,

PFA

Re: How to display first and last value ?

Digvijay_Singh — Mon, 20 Jun 2016 09:21:56 GMT

Try this -

Re: How to display first and last value ?

qliksus — Mon, 20 Jun 2016 09:56:49 GMT

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) )

Re: How to display first and last value ?

Digvijay_Singh — Mon, 20 Jun 2016 10:32:53 GMT

Implemented Start Date and End Date selection logic -

Re: How to display first and last value ?

Mon, 20 Jun 2016 13:48:56 GMT

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	Closing Balance
A	X	01/04/2016	100000	2000	10200
A	Y	01/04/2016	2000	0	2000
B	X	01/04/2016	3000	200	3200

Re: How to display first and last value ?

Digvijay_Singh — Mon, 20 Jun 2016 16:39:23 GMT

I think the one I posted has this feature

Re: How to display first and last value ?

Tue, 21 Jun 2016 03:31:04 GMT

yes, its working. thank you