Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Dimension: Year
Measure: RangeSum(Above(Sum([Count of data]), 0, RowNo()))
Hope it helps!
Dimension: Year
Measure: RangeSum(Above(Sum([Count of data]), 0, RowNo()))
Hope it helps!
Thank you so much!
This helped !
Do you mind breaking down the code so i can also understand it better?
Again thank you !
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.
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;
@shetty_1 , your method works, but there are some limitations and things to take into account:
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.
Hi TimvB,
Thanks for the solution
Thank you this has been very informative indeed !