# Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
New Contributor II

## New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

Greetings to Qlik Cognoscenti.

I have a question, and if anyone could shed light, I would appreciate it deeply.  I'm a relative Qlik noob, and I thus apologize if my inquiry is overly simple.

The topic has to do with summing a field under certain conditions.  I'd STRONGLY prefer to accomplish this in the data load rather than an active Qlik expression, for I fear the latter may hamper performance.

Consider the following inline data load:

TestData:

LOAD * INLINE [

RecNum, Contract_Num, Period, Amount, ForwardCumulAmt

1,    Contract001, 201801, 11, 15

2,    Contract001, 201802, -3, 4

3,    Contract001, 201803, 6, 7

4,    Contract001, 201804, 1, 1

5,    Contract002, 201801, 77, 106

6,    Contract002, 201802, 14, 29

7,    Contract002, 201803, -6, 15

8,    Contract002, 201803, 20, 15

9,    Contract002, 201804, 1, 1

];

The goal here is to have the Qlik data load calculate the field ForwardCumulAmt.  I provided that field for these nine sample rows merely to help explain the question. When in production, that data element will not be available.  Rather, it must be calculated.  Following is how the field is calculated for just a few rows:

For RecNum 1:  Sum (11, -3, 6, 1) = 15

For RecNum 2:  Sum (-3, 6, 1) = 4

Algorithm in words:  Sum across Amount field where:

(A) Contract_Num is the same as Contract_Num for current record

AND

(B) Period >= Period for current record

Two more examples:

For RecNum 7: Sum (-6, 20, 1) = 15

For RecNum 8: Sum (-6, 20, 1) = 15

NB:  Please note that field Period for RecNum 7 and RecNum 8 happens to be the same (i.e., 201803).  This is not an error on my part.  It is a necessary characteristic of the data.

Many thanks in advance.

Cheers!

Regards,

Chuck

Tags (4)
1 Solution

Accepted Solutions
Valued Contributor II

## Re: New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

Hi Charles,

Try this:

```TestData:
LOAD * INLINE [
RecNum,Contract_Num,Period,Amount,ForwardCumulAmt
1,Contract001,201801,11,15
2,Contract001,201802,-3,4
3,Contract001,201803,6,7
4,Contract001,201804,1,1
5,Contract002,201801,77,106
6,Contract002,201802,14,29
7,Contract002,201803,-6,15
8,Contract002,201803,20,15
9,Contract002,201804,1,1
] (delimiter is ',');

Final:
RecNum,
Contract_Num,
Period,
Amount,
ForwardCumulAmt as data_test
Resident TestData
order by Contract_Num, Period desc;

tmp:
Contract_Num,
Period,
sum(Amount) as Amount_period
Resident TestData
Group by Contract_Num,
Period
order by Contract_Num,
Period desc;

drop table TestData;

Left join(Final)
Period,
RangeSum(Amount_period, if(Peek('Contract_Num')=Contract_Num,Peek('ForwardCumulAmt'))) as ForwardCumulAmt
Resident tmp;

drop table tmp;
```

qcf is attached.

I didn't drop the original ForwardCumulAmt field (rename to data_test) for testing purpose.

G.

3 Replies
Valued Contributor II

## Re: New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

Hi Charles,

Try this:

```TestData:
LOAD * INLINE [
RecNum,Contract_Num,Period,Amount,ForwardCumulAmt
1,Contract001,201801,11,15
2,Contract001,201802,-3,4
3,Contract001,201803,6,7
4,Contract001,201804,1,1
5,Contract002,201801,77,106
6,Contract002,201802,14,29
7,Contract002,201803,-6,15
8,Contract002,201803,20,15
9,Contract002,201804,1,1
] (delimiter is ',');

Final:
RecNum,
Contract_Num,
Period,
Amount,
ForwardCumulAmt as data_test
Resident TestData
order by Contract_Num, Period desc;

tmp:
Contract_Num,
Period,
sum(Amount) as Amount_period
Resident TestData
Group by Contract_Num,
Period
order by Contract_Num,
Period desc;

drop table TestData;

Left join(Final)
Period,
RangeSum(Amount_period, if(Peek('Contract_Num')=Contract_Num,Peek('ForwardCumulAmt'))) as ForwardCumulAmt
Resident tmp;

drop table tmp;
```

qcf is attached.

I didn't drop the original ForwardCumulAmt field (rename to data_test) for testing purpose.

G.

New Contributor II

## Re: New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

NICELY done, Gabor.

It took me two passes to discern your strategy.  But when I did, I saw the clean sense of it.  (I hope you make it to Qlik community level 8 very soon!)

MANY thanks!

Valued Contributor II

## Re: New to Qlik Sense. Unsure how to do something. Probably SET or AGGR.

Thank you Charles