Why is it not possible to sum (or aggregate) various master measures?

[retretrt]

I have 3 master measures: Total Sales, Weight and Return. The Total Sales master measure is the sum of Weight*Return master measures. The following are the formulas for each master measure:

Weight (Master measure):  aggr(Sum(WGT_SALES) / Sum({1} total <DATE_TIME> WGT_SALES), COMPANY_NAME, DATE_TIME, INDUSTRY)

Return (Master measure): aggr((({1}SALES) / ({1}SALES_previous_day)) - 1, COMPANY_NAME, DATE_TIME, INDUSTRY) 

Total Sales (Master measure): Weight*Return 

In the formula editor I type the formula:

sum ({1} total <DATE_TIME> Total Sales)

, which I want to display as a column in a table. However, when using an aggregator like Sum (or Max, Min, Avg etc.) I get an error in the expression. In the formula editor it just turns "black" (as in not active/cant be read by the editor), and not purple-colored which is the default color for master measure fields.  

If write out the entire formula without using master measures, it works fine. The "written out" formula would look like this:

sum ({1} total <DATE_TIME> aggr(Sum(WGT_SALES) / Sum({1} total <DATE_TIME> WGT_SALES), COMPANY_NAME, DATE_TIME, INDUSTRY) * aggr((({1}SALES) / ({1}SALES_previous_day)) - 1, COMPANY_NAME, DATE_TIME)

Why is it not possible or an error to sum (or aggregate) various master measures? 

[sbaro_bd · Accepted solution]

Hello @retretrt ,

If it doesn't work that means it's not possible to do that (currently).

According to my knowledge, you can reuse the master items measures to perform some basic calculations like addition or division. Encapsulate your master item measure in an aggregate function doesn't work unfortunately. 

Regards.

[rwunderlich]

The syntax does not allow for a Measure name inside an aggregation function.  A Measure reference returns a scalar (single) value. 
https://help.qlik.com/en-US/sense/August2023/Subsystems/Hub/Content/Sense_Hub/Expressions/references...

As the Measure is already aggregated, it cannot be aggregated again, potentially with a narrower grain or different set of values.

As a workaround for what you want to do, you can define your expressions in variables. Then use the variables as the Measure definition.  Then you reference the Variables instead of the Measures when you need to do an additional aggregation.

BTW, not sure why you are wanting to enclose your example in the Sum(). Is it because you are wanting to specify the TOTAL<> parameter?

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[richardwilde]

Yeah, that sure makes life difficult not being able to aggregate the master measure. I now have a whole pile of them and assumed I could just sum them for a pivot table, but now that won't work.

Even if I pull out the definition of the master measure, it is still often referring to other master measures in turn, so I still can't sum without pulling out definitions within definitions within definitions... really heavily reduces the modularity that this was supposed to create.

I guess I can use variables, but it's a pain to switch, and the variables are not as accessible. Ideally it would display the meaning of a master measure or variable if you hovered over it or something, similar to evaluate in Excel (F9). Otherwise it becomes really hard to remember what each measure or variable actually means.

[rwunderlich · Accepted solution]

Can you give an example of how you want to aggregate master measures? If I want to "sum" two master measures I use addition.

[COGS] + [Margin]

-Rob