Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
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
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

Highlighted
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