Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ccharalamb005
Contributor
Contributor

Aggregate question about dimensions

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 datayear
52017
52018
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)
1 Solution

Accepted Solutions
TimvB
Creator II
Creator II

Dimension: Year

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

Hope it helps!

View solution in original post

7 Replies
TimvB
Creator II
Creator II

Dimension: Year

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

Hope it helps!

ccharalamb005
Contributor
Contributor
Author

Thank you so much! 
This helped ! 

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

 

Again thank you ! 

TimvB
Creator II
Creator II

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

Dim: YearMeasure: Sum([Count of data])
20175
20185
20195

 

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: YearMeasure: RangeSum(Above(Sum([Count of data]), 0, RowNo()))
20175
201810
201915

 

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
Partner - Contributor II
Partner - Contributor II

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
Creator II
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.

shetty_1
Partner - Contributor II
Partner - Contributor II

Hi TimvB,

 

Thanks for the solution

ccharalamb005
Contributor
Contributor
Author

Thank you this has been very informative indeed !