Qlik Community

Qlik Design Blog

All about product and Qlik solutions: scripting, data modeling, visual design, extensions, best practices, etc.

Employee
Employee

Full Accumulation example

During the last days we have been working in a new visualization piece that will be live soon in The Telegraph website. We got some data with daily refugees arrivals to different countries, the goal was to visualize how many people have moved across Europe during the last few months in a map, but we also wanted to plot a line/area chart with the cumulative number of arrivals per country and per day so you can see trends and evolution over time. Our data set included daily data but it didn't contain cumulative numbers so we had to calculate them.


The calculation should be something like this:


chart1.jpg

If is the first row or country is different than previous row country, then load the field value, else load the field value plus same field previous row value.

Step 1 Data Load

Start by loading our data table with the daily arrival values by country.

//Load the main table

DataLoad:

LOAD

    "Date",

    Country,

    "Daily Estimated Arrivals"

FROM [lib://LibraryName/Data.qvd] (qvd);

The generated table will look like the picture below:

chart2.png

As you can notice the data is not sorted in a way that will let me successfully calculate the new field as I designed it because it will mix data from different countries, so my next move will be to sort the table by country and date.

Step 2 Sorting the table

//Sorting the table

SortLoad:

NoConcatenate Load

  "Date",

    Country,

    "Daily Estimated Arrivals"

Resident DataLoad

Order by Country, Date;

Drop table DataLoad; //Deletes the previous table no longer needed

Because I’m using a Resident Load statement to load and sort the values from the previous table, and both tables have identical field sets, I need to specify NoConcatenate before the load, otherwise both tables will be automatically concatenated. Using ‘Order By’ clause will sort the table by Country and then by Date. Finally, because the first table DataLoad will be no longer needed I’ll delete it using Drop Table.

My new table will be as in the picture below

chart3.png

Step 3 Adding the calculated new field

Now that we have the data table ready we can finally calculate the new field to the table containing the cumulative daily estimated arrivals. To do so we will use the peek and previous script functions. Both functions are similar but they have some key differences that you should learn, please don’t miss this blog post:  Peek vs Previous: when to use each

//Add cumulative data to the table

CummulativeLoad:

NoConcatenate Load

    "Date",

    Country,

    "Daily Estimated Arrivals",

    If(RowNo()=1 Or Country<>Previous(Country),

          "Daily Estimated Arrivals",

          "Daily Estimated Arrivals" +  Peek("Cumulative Daily Estimated Arrivals", RowNo()-2))

    as "Cumulative Daily Estimated Arrivals"

Resident SortLoad;

Drop table SortLoad; //Deletes the previous table no longer needed

chart4.png

Click on the image below to see how we created the new field to match with our calculation statement.

expression.jpg

The final result will be a data table containing the fields to support both daily and cumulative charts.

result.gif

Enjoy Qliking!

AMZ

(Note: I divided this example in 3 steps so hopefully it is clearer, but step 2 and 3 can be merged in one single step)

21 Comments
MVP & Luminary
MVP & Luminary

Hi Arturo,

Thanks for sharing these insights.  I find Peek very useful for finding differences between values on two rows, but seldom use it for accumulations.

I did a blog post on accumulating by associating each day with every day prior to it in the data model:

http://www.quickintelligence.co.uk/qlikview-accumulate-values/

This same approach can be used for average bars and moving annual totals as well.

Hope it is of interest to others.

Steve

690 Views
Employee
Employee

Thanks for sharing Steve, very interesting stuff.

In this particular example peek works great because it will let me target a field that has not been previously loaded in the table and  target a specific row.

For partial accumulations I'd also suggest this post The As-Of Table

690 Views
promaniuk
New Contributor III

Nice, Thanks for sharing.

690 Views
MVP & Luminary
MVP & Luminary

If you use RangeSum instead of + operator then you don't need to check for rowno()=1. You can also let peek use its default -1 offset to look up the previous value instead of calculating an absolute record number.

    If(Country<>Previous(Country), 

          "Daily Estimated Arrivals"

          RangeSum("Daily Estimated Arrivals" ,Peek("Cumulative Daily Estimated Arrivals"))) 

    as "Cumulative Daily Estimated Arrivals" 

690 Views
Employee
Employee

Thanks Gysbert! I like this one, it simplifies the expression removing the rowno()-2 and makes it more descriptive. Thanks for sharing.

690 Views
ecolomer
Honored Contributor II

Good work, very useful.

Thank's for sharing

Enrique Colomer

690 Views
Partner
Partner

     Ass Always! Great work Arturo!

thanks for share

Fernando K.

690 Views
Partner
Partner

Very nice! 

The thing is that QlikView is not a cube-based app. Totals are calculated in expressions. And that’s the
good thing. Let's say you have a new filter, besides of Country, Air Company, how would you do that on the script? Many more combination of totals, or accumulative calculations have to be done. Imagine that we have 10 filters. That's why the best thing to do, in my opinion, is calculate in design, rather than in the script.

Christian.

SalU2 Arturo.

0 Likes
690 Views
robert99
Valued Contributor II

Is it possible to drill down (say country --> City) using a script approach?

I guess just do the above twice?

I have done it as shown on this thread but is there a different way

NB I never used this approach because drilling down would not be possible it seemed to me

https://community.qlik.com/blogs/qlikviewdesignblog/2015/11/09/accumulations#comment-42554

0 Likes
690 Views
Employee
Employee

Christian, design calculations, when available, will usually give you more flexibility and dynamism than Script based calculations that's right. However they will not always be available nor give you the result you were expecting, if you follow @stevedark's link in the first comment you will find that design full accumulation calculation not always works as you want it to do.

0 Likes
690 Views
Employee
Employee

I guess you could still use this approach when your dimension is a drill down, yes. You will need to tune your chart so the expression change based on the drill down element currently active. It will also require you to create as many new cumulative fields in the data model as drill down dimensions used in the chart.

0 Likes
690 Views
robert99
Valued Contributor II

But I guess if you made a selection for the last month it would not give the right result

I personally feel that doing this as an expression would be the way to go most times. This is covered under Henric's above blog post. Or have I missed something?

0 Likes
690 Views
Partner
Partner

That's what I'm saying.
It's impossible to have all the combinations done, before the user decides what to have.

As a script exercise is very good.

In my opinion, the best way to solve the accumulation problem is using inter-record funcktions, specifically, rangesum() funcktion.

HIC’s blog about it, has been mentioned in this one.


SalU2


0 Likes
690 Views
Employee
Employee

As described in the post, the script calculation is perfectly valid and simple enough to my requirements. Under different business needs though, I would always take a moment to consider what my options are. The best solution might be the As-of table, it might be using full accumulation in design, or it might be something else. Luckily Qlik give us several options to pick from.

0 Likes
690 Views
Partner
Partner

What about this in Sense?

0 Likes
690 Views
robert99
Valued Contributor II
0 Likes
690 Views
Employee
Employee

We don't have full accumulation in design yet in Qlik Sense that's why I think it could be useful to have a quick and easy method for creating a full accumulation in the load script. Again, this method won't work for every use case scenario.


Also I feel the example is a great script example to learn more about Peek() and Previous(), what they are and how they work.

0 Likes
690 Views
Not applicable

Nice post

0 Likes
690 Views
Partner
Partner

Arturo Muñoz escribió:

We don't have full accumulation in design yet in Qlik Sense that's why I think it could be useful to have a quick and easy method for creating a full accumulation in the load script. Again, this method won't work for every use case scenario.


Also I feel the example is a great script example to learn more about Peek() and Previous(), what they are and how they work.

See yet in your message is a good new

Thanks

0 Likes
690 Views
Not applicable

It wouldn't be more efficient if we use rangesum?

0 Likes
690 Views
Employee
Employee

Hi Nisim,

RangeSum would be another way to get the same result yes, it might be a more elegant solution (check Gysbert comment), but I don't think we would find any optimization or efficiency gain by using it.

0 Likes
690 Views