topic Re: How do I aggregate total data to a dimension based on a dynamic condition? in App Development

How do I aggregate total data to a dimension based on a dynamic condition?

Thomasowich — Wed, 11 Jan 2023 16:22:28 GMT

I have a table consisting of three columns: Location, Date, Value. Each location appears exactly once.

I want to add a new column Aggr which for each location adds up Value of all other locations whose date is higher than its own date. Here is an example:

Location	Date	Value	Aggr
USA	01.01.2022	1	10
Germany	03.03.2022	2	0
Japan	01.01.2021	4	11
China	02.02.2022	8	2

How do I do this in Qlik?

Re: How do I aggregate total data to a dimension based on a dynamic condition?

maxgro — Wed, 11 Jan 2023 20:50:04 GMT

try with

T:
LOAD * INLINE [
Location, Date, Value, Aggr
USA, 01.01.2022, 1, 10
Germany, 03.03.2022, 2, 0
Japan, 01.01.2021, 4, 11
China, 02.02.2022, 8, 2
];

JOIN (T)
LOAD Location as Location1,
Date as Date1,
Value as Value1
RESIDENT T;

Z:
LOAD Location,
Date,
ONLY(Value) as Value,
ONLY(Aggr) as Aggr,
SUM(IF(Date1>Date, Value1)) as NewAggr
RESIDENT T
WHERE Date1 >= Date
GROUP BY Location, Date
;

DROP TABLE T;