Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ganeshreddy
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
kfoudhaily
Partner - Creator III
Partner - Creator III

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

regards,

QlikView Qlik Sense consultant
isingh30
Specialist
Specialist

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

ganeshreddy
Creator III
Creator III
Author

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

is this what you need?

Capture.PNG !

please mark as helpfull if it is for you.

regards,

QlikView Qlik Sense consultant
ganeshreddy
Creator III
Creator III
Author

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

can anyone help me on this?

vishsaggi
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

shiveshsingh
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