Set analysis using multiple variables

[burgersurfer]

I created a variable that calculates net unit sales and prefer to use variable through all my expressions.

The variable calculates e.g. net units for a specific finYear. Now, I want to use this variable in a straight table to calculate the daily sales of certain products. That is, for every day Monday-Sunday, give me the sales

This formula works:

sum({$<FinYear = {'$(vPY0)'}, CalendarWeekDay = {'Mon'}, TD_TYPE ={'0', '34'} >} TD_QTY) - sum({$<FinYear = {'$(vPY0)'}, CalendarWeekDay = {'Mon'}, TD_TYPE ={'1', '35'} >} TD_QTY)

But everything other than CalendarWeekDay is already in my variable:

vNetUnitsTY = sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'0', '34'} >} TD_QTY) - sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'1', '35'} >} TD_QTY)

How do I express the formula using vNetUnitsTY and my time calculation?

Tried the following to no avail:

sum({<CalendarWeekDay = {'Mon'}>} $(vNetUnitsTY))

sum({<CalendarWeekDay = {Mon}>} $(vNetUnitsTY))

sum({$<CalendarWeekDay = {'Mon'}>} $(vNetUnitsTY))

sum({$<CalendarWeekDay = {Mon}>} $(vNetUnitsTY))

[#2]

I think the problem you are running into is a sum inside of another sum. That is not allowed. You can use the Aggr() function to do nested aggregates, but you need to define the dimensions and we don't know what your dimensions are.

The easiest solution, why not just make a copy of your vNetUnitsTY variable that includes the CalendarWeekDay set as well?

vNetUnitsTYCal = sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'0', '34'},
CalendarWeekDay = {'Mon'}>} TD_QTY) -
sum({$<FinYear = {'$(vPY0)'}, TD_TYPE ={'1', '35'},
CalendarWeekDay = {'Mon'} >} TD_QTY)

[burgersurfer]

Thanks NMiller, but from a maintenance point of view I prefer have one variable, and change it on one place when necessary. My dimensions are a list of products (ItemCode) and their sizes (SZ)

[#4]

Try:

Sum({<CalendarWeekDay = {'Mon'}>} Aggr($(vNetUnitsTY), ItemCode, SZ))

I tested this on a simplified example and it appeared to work.

[burgersurfer]

Thanks but somewhere my data works different, I get incorrect results.

Opted for writing out the code in the end.

[#6]

Yeah, that is probably the easiest way to do it.

On the Aggr function, the first parameter is your expression (variable). The subsequent parameters are the dimensions you want to group by when doing the calculation. You may need to modify which ones are used to get to the right result.

I only use Aggr when I have no other choice.