Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Talk to Experts Tuesday, Live Q&A, September 22: Moving from QlikView to Qlik Sense. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator III
Creator 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
Creator III
Creator III

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

regards,

Highlighted
Specialist
Specialist

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

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

is this what you need?

Capture.PNG !

please mark as helpfull if it is for you.

regards,

Highlighted
Creator III
Creator III

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
Creator III
Creator III

can anyone help me on this?

Highlighted
Champion III
Champion III

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

Highlighted
Master
Master

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