Aggregate question about dimensions

[ccharalamb005]

Hello everyone ,

I came upon a problem which i'm sure a lot of Qlik qurus can help me with !

I am trying to find a way to aggregate data of previous years 

for example if i have a dataset like this:

Count of data	year
5	2017
5	2018
5	2019

My points on a line chart should look like this :

2019 = 15
2018 = 10
2017 = 5

Any suggestions ? This will help out a lot as i don't seem to find an easy solution

Thank you so much

[TimvB · Accepted solution]

Dimension: Year

Measure: RangeSum(Above(Sum([Count of data]), 0, RowNo()))

Hope it helps!

[ccharalamb005]

Thank you so much! 
This helped ! 

Do you mind breaking down the code so i can also understand it better?

Again thank you ! 

[TimvB]

Your initial data looks like below if you visualize it in a straight table:

Dim: Year	Measure: Sum([Count of data])
2017	5
2018	5
2019	5

In essence, a line chart visualizes the data of a straight table. Thus, we need to accumulate the measure Sum([Count of data]). In your case, the end result of the line chart's straight table is as follows:

Dim: Year	Measure: RangeSum(Above(Sum([Count of data]), 0, RowNo()))
2017	5
2018	10
2019	15

You can check the result by exporting the line chart. 

I will now break down the measure RangeSum(Above(Sum([Count of data]), 0, RowNo())):

Above(<expr> [ , offset [, count]]) evaluates an expression at a row above the current row within a column segment in a table. The row for which it is calculated depends on the value of offset, if present, the default being the row directly above. For charts other than tables, Above() evaluates for the row above the current row in the chart's straight table equivalent. We need the above() function to evaluate all rows above  including its own row. We do this by using: Above(<expr>, 0, RowNo()). This will calculate the expression for every row from row 0 up to and including its own row, i.e., RowNo(). Thus, it will return an array of values of the size of the number of rows in the straigh table.

Lastly, we use RangeSum() to calculate the sum of a range of values, in this case the array that the function above returned. This makes sure that the expression is accumulated over the years.

You can also check the documentation of functions in scripts and chart expressions in Qlik for more explanations and examples.

I hope this helps.

[shetty_1]

Other Method:

test:
load * Inline
[
Data,Year
5,2017
5,2018
5,2019
];

NoConcatenate
abc:
load
Year,
if(Year='2017',Data,
if(Year='2018',Data+Peek(Data1),
if(Year='2019',Data+Peek(Data1)
,0))) as Data1
Resident test;

drop Table test;

[TimvB]

@shetty_1 , your method works, but there are some limitations and things to take into account:

• You first need to group the data by year
• It is necessary to sort the resident table by year (ascending), otherwise the peek function accumulates the wrong year
• It creates an extra field in the data model which can be confusing
• In the front end it is not possible to apply filters, as the data is static.

Your solution might however work well when there is a lot of data in the data model (let’s say > 1 GB) and no filter options are needed.

[shetty_1]

Hi TimvB,

Thanks for the solution

[ccharalamb005]

Thank you this has been very informative indeed !