Line chart showing just one line when using a measure

[guscavge]

Qlik Sense beginner here. I've looked around in the forums but not really found an answer to this issue I've been having.

I have created two measures: one which calculates the costs of a company that are personnel costs over the past 12 months, and one which calculates the total costs of a company over the last 12 months. The syntax of the measures is as follows (essentially copied from a guide to rolling x-months calculations):

Sum(Aggr(RangeSum(Above(total sum({<Year,Cost_type={'Personnel'},Period=>}Amount),0,12)),Period)) for personnel costs

Sum(Aggr(RangeSum(Above(total sum({<Year,Period=>}Amount),0,12)),Period)) for total costs

My goal with these measures is to show the % of the company's costs over the past 12 months that are personnel costs, so in charts I divide the first measure by the second one. I have used these measures in a line chart that has the months (January-December) along the x-axis and one line for each year, so 2022, 2023, 2024 and so on each have their own line. That works fine.

However, then I tried to use the same measure in a line chart that has Period (so for example 202201, 202202, 202203 and so on) along the x-axis, and then one line for each Region of the company, so North, West, East and South each have their own line. For some reason this brings up just one line that seems to be a total of all the regions put together, instead of one line for each region. 

What am I doing wrong? I suspect the issue might be that the measure is calculating different values for each year but not for each region, which means that I can then produce different lines for different years but not for different regions. But I'm not sure if that actually is the issue here, and I'm not good enough at syntax to play around with the measures with any degree of confidence.

Thanks in advance!

[BIAKS]

I would suggest duplicate your chart and drop a table onto it. By doing that you will convert it to a table so you can see all the values. For example:

Region	Period	Sum(Amount)	Above(sum(Amount))	RangeSum(Above(total sum({<Year,Period=>}Amount),0,12))	Sum(Aggr(RangeSum(Above(total sum({<Year,Period=>}Amount),0,12)),Period))
North	202201	100	-	100	375
South	202201	150	100	250	375
West	202201	125	150	375	375

If you can split your formula, you can see where it becomes a problem.

[lennart_mo]

Hi @guscavge, 

to help you tackle your problem, you first need to understand what the aggr() function does.

It basically builds a table in the background, containing one measure and as many dimensions as you define. The resulting table is the underlying basis for the outer calculation. So if we look at your total cost calculation i.e.:

Aggr(RangeSum(Above(total sum({<Year,Period=>}Amount),0,12)),Period)

you'd get a straight table with "Period" as a dimension and RangeSum() as the measure. If you take that data and try to aggregate it per Period and Region, you logically only get one value per Period, since there's no Region given.

So to solve your problem, add your "Region" field to the aggr() as a dimension like so:

Sum(Aggr(RangeSum(Above(total sum({<Year,Cost_type={'Personnel'},Period=>}Amount),0,12)),Period,Region)) 

Sum(Aggr(RangeSum(Above(total sum({<Year,Period=>}Amount),0,12)),Period,Region))

Hope this helps!

Best regards

Lennart

[guscavge]

That seems to almost do the trick - I get one line for each Region now - but not entirely. For some reason the figures I get in the chart are now correct if I use the filter to pick just one Region out of the six Regions that are in the dataset. But if I show all regions, or filter more than one Region, then the figures are wrong for some reason.

I've inspected the measure in a table and it looks like for some reason the second measure (personnel costs) gives a considerably smaller sum for each combination of Region and Period if I have multiple Regions selected, whereas the first measure (all costs) gives a considerably larger sum if I have multiple Regions selected. So the figure for personnel costs as a percentage of total costs appears lower than in actual fact (unless, as mentioned, I pick only one Region in which case the figure is correct). What could be causing this issue?

[lennart_mo]

Ah, right. You might have to alter the dimensions to structured parameters with predefined sorting.

So instead of Period and Region you should use (Period, (Numeric, Ascending)) and (Region, (text, Ascending)), I just assume the region values are alphabetic.

Without the sorting the order for Above() might be wrong.

Taking a look at the official documentation might be helpful as well, especially the last example:
Aggr - chart function | Qlik Sense on Windows Help

[guscavge]

Thank you. Unfortunately that didn't seem to make any difference. The measures still give different figures depending on whether I'm only selecting one region or whether I've selected multiple regions (in which case the total cost measure shows too large a number, and the personnel costs measure shows too small a number).

Could it be that I'm adding the "(Period,(Numeric,Ascending))" in the wrong part of the formula? The measures after your advice and reading into the official documentation is now:

Sum(Aggr(RangeSum(Above(total sum({<Year,Cost_type={'Personnel'},Period=>}Amount),0,12)),(Period,(Numeric,Ascending)),(Region,(text,Ascending)))) 

Sum(Aggr(RangeSum(Above(total sum({<Year,Period=>}Amount),0,12)),Period,Region))

[lennart_mo]

Okay. First of all, you got the structured parameters right, but you'll need to add them to the total costs as well.

Could you create a straight table with Period and Region as Dimensions and the innermost sums as measures as well as the complete aggr()?

Like so:

Period	Region	sum({<Year,Cost_type={'Personnel'},Period=>}Amount)	Sum(Aggr(...)) Personnel	sum({<Year,Period=>}Amount)	Sum(Aggr(...)) Total

Sort the table by Period and region and compare the values you receive to what you'd expect, without any selections. 
From what I've tested so far, the "total" qualifier in the Above() causes some problems, since the formula ignores switches between regions in that case. So when you go from region A to B, the first period is the sum of the last 11 periods for A + the first for B. So maybe try removing the "total" and see if the values change.

Let me know if and where you found values deviating from what you expected!