# New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for
Did you mean:
Contributor

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

Labels (1)
• ### Qlik Sense

1 Solution

Accepted Solutions
Creator II

Dimension: Year

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

Hope it helps!

7 Replies
Creator II

Dimension: Year

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

Hope it helps!

Contributor

Thank you so much!
This helped !

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

Again thank you !

Creator II

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.

Partner

Other Method:

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

NoConcatenate
abc:
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;

Creator II

@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.

Partner

Hi TimvB,

Thanks for the solution

Contributor

Thank you this has been very informative indeed !

Tags