Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

ganeshreddy
Contributor III

Aggregation with latest transaction.

Hi All,

How to achieve the second table, I need to show both in the dashboard.

T Number

Date

m1

m2

0003 CM 1

17/08/2015

0

0

4 CM 1

22/01/2016

0

0

5 CM 1

19/02/2016

0

3,502

6 CM 1

19/07/2016

0

3,809

7 CM 1

19/09/2016

0

5,350

8 CM 1

04/10/2016

0

5,694

9 CM 1

19/10/2016

0

5,694

10 CM 1

09/01/2017

0

5,690

11 CM 1

20/01/2017

0

86,687

12 CM 1

28/03/2017

0

8,605

13 CM 1

18/05/2017

0

8,219

14 CM 1

15/06/2017

0

3,908

Team

Tra Reference

Group Name

Status

Amount (Latest m1 +m2 from first table)

Pro Zen

30732AB

JRB

OPEN

3,908

Thanks,

Ganesh

8 Replies
Highlighted
kfoudhaily
Contributor III

Re: Aggregation with latest transaction.

more details please about your data and what you need to do.

regards,

isingh30
Valued Contributor

Re: Aggregation with latest transaction.

Your requirement is not clear. Can you share sample application or data in excel format?

ganeshreddy
Contributor III

Re: Aggregation with latest transaction.

Hi All,

I got data as shown in attached sheet1 and I need result as Sheet2, This needs a runtime calculation, an expression will be very helpful or guide me to achieve this in script level.

Thanks,

Ganesh

kfoudhaily
Contributor III

Re: Aggregation with latest transaction.

is this what you need?

Capture.PNG !

please mark as helpfull if it is for you.

regards,

ganeshreddy
Contributor III

Re: Aggregation with latest transaction.

Hi Kh fou,

I need last record which is latest, the sum of m1 and m2 i.e. 0+3908 = 3908, Please refer to the data provided in the thread.

Result:

     

TeamTraReferenceGroup NameStatusAmount (Latest m1 +m2 from first table)
Pro Zen30732ABJRBOPEN3,908

Thanks,

Ganesh

ganeshreddy
Contributor III

Re: Aggregation with latest transaction.

can anyone help me on this?

vishsaggi
Esteemed Contributor III

Re: Aggregation with latest transaction.

Try this?

Team:

LOAD *, Num(Floor(Date#(Date, 'DD/MM/YYYY'))) AS DateId;

LOAD [T Number],

     Date,

     m1,

     m2,

     Team,

     [Tra Reference],

     [Group Name],

     Status

FROM

(ooxml, embedded labels, table is Sheet1);

LET vMaxDateId = Peek('DateId', -1);

Final:

LOAD [T Number],

     IF(DateId = '$(vMaxDateId)', 1, 0) AS MaxFlag

Resident Team;

Then use straight table and add dims and expr like below:

Capture.PNG

shiveshsingh
Honored Contributor

Re: Aggregation with latest transaction.

Try this

T:

LOAD [T Number],

date(alt(Date#(Date,'DD/MM/YYYY'),Date#(Date,'DD-MM-YYYY')),'DD/MM/YYYY')AS Date,

     m1, m2, Team,

     [Tra Reference],

     [Group Name], Status

FROM doc.xlsx

(ooxml, embedded labels, table is Sheet1);

Expression : =sum({<Date={"$(=max(Date))"}>}m1)+sum({<Date={"$(=max(Date))"}>}m2)

Capture.JPG