topic Re: Aggregation with latest transaction. in QlikView

Aggregation with latest transaction.

ganeshreddy — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Aggregation with latest transaction.

kfoudhaily — Mon, 11 Jun 2018 14:39:30 GMT

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

regards,

Re: Aggregation with latest transaction.

isingh30 — Mon, 11 Jun 2018 15:04:20 GMT

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

Re: Aggregation with latest transaction.

ganeshreddy — Wed, 13 Jun 2018 08:37:12 GMT

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

Re: Aggregation with latest transaction.

kfoudhaily — Wed, 13 Jun 2018 09:11:29 GMT

is this what you need?

please mark as helpfull if it is for you.

regards,

Re: Aggregation with latest transaction.

ganeshreddy — Wed, 13 Jun 2018 09:24:49 GMT

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:

Team	TraReference	Group Name	Status	Amount (Latest m1 +m2 from first table)
Pro Zen	30732AB	JRB	OPEN	3,908

Thanks,

Ganesh

Re: Aggregation with latest transaction.

ganeshreddy — Wed, 13 Jun 2018 14:15:42 GMT

can anyone help me on this?

Re: Aggregation with latest transaction.

vishsaggi — Wed, 13 Jun 2018 15:04:00 GMT

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:

Re: Aggregation with latest transaction.

shiveshsingh — Wed, 13 Jun 2018 17:29:34 GMT

Try this

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)