topic Re: Cumulative YTD Calculation in Line Chart in App Development

Cumulative YTD Calculation in Line Chart

InsightSeeker — Thu, 26 Dec 2024 18:24:28 GMT

Hello everyone,

I would like to create a visualisation in Qlik Sense that calculates the cumulative number of incidents for each year. What sounds simple at first, presents me with major challenges when developing a suitable key figure. The following criteria must be covered by the formula of the key figure:

The calculation should start from zero on 1 January of each year
The number of incidents (Count(IncidentID)) must be calculated cumulatively within a year
The key figure must be presented in the form of a line chart
A drill-down dimension with year, month and date serves as the dimension
The value must not change if a date is selected

Two tables are available as a database for this, which I have mimicked in the attached sample application. One table, ‘Calendar’, contains the relevant date fields, while the ‘Incidents’ table contains the unique IDs of the incidents.

So far I have tested the following formula:
RangeSum(Above(Count(IncidentID),0,RowNo()))
The result is already very close to the target - the only problem is that the calculation does not start from zero on 1 January each year. Addtionally, it is calculated dynamically in the case of date selections.

As a result, based on the sample data, I expect the chart to show 6 as the cumulative number of incidents for 31/12/2023, 0 as the cumulative number for 01/01/2024 and 8 as the cumulative number for 14/02/2024, for example.

I would be pleased if someone could help me. Many thanks in advance.

Re: Cumulative YTD Calculation in Line Chart

pallavi_96 — Fri, 27 Dec 2024 06:06:54 GMT

Hi @InsightSeeker

You can use the following expression to obtain the desired output:

Aggr(RangeSum(Above(count(IncidentID), 0, RowNo())), Year,[%Key_Date])

Re: Cumulative YTD Calculation in Line Chart

InsightSeeker — Tue, 07 Jan 2025 20:14:01 GMT

Hi @pallavi_96

It's me again. I've returned to work after the holidays and wanted to use your solution, which worked great with the sample data. Unfortunately, I ran into two major problems:

The formula does not work correctly when using a time-based drilldown dimension in the line chart (year, month, day) - see image: result if the dimension is at monthly level. Again, at monthly level the calculation should start from zero on 1 January of each year but only display the accumulated number of incidents at the end of the month. Based on the example data provided, I would expect 6 as the result for January and 8 as the cumulative number for February.
The formula performs poorly with larger amounts of data - I suspect this is due to the Aggr() function

Can you please provide further support in the form of an adapted formula or an alternative approach? Or maybe someone else?

Thanks again in advance! Best regards.