Dynamic aggregate value at the row level

[Swang]

Hi All,

I have read many posts, but I can't find the answer. Please help me solve this problem, thanks.

I want to achieve dynamic aggregation at the row level. I have some raw data as attached file. The data has five dimensions (code, group, brand, product, Yearmonth, rolling), and the measurement is value.

Calculation Logic:

1. There is a straight table whose dimensions are code and product. When Yearmonth is selected, judge the data of each row, find the largest Yearmonth with value greater than 0, and then summarize value from this month and the previous two months.

2. Then calculate the maximum value of the selected Yearmonth, subtract the maximum Yearmonth of each row, and judge whether it is greater than or equal to 3. If so, the number of months is equal to the result of the subtraction subtract 2.

3. The final calculation result should be the result of step 1 divided by 3 and multiplied by the result of step 2.

My expression :
=Sum({$<Rolling= {"$(='>=' & Max(Aggr(Max(If(Value>0, Rolling))-2,Code,ProdGroup))) $(='<=' & Max(Aggr(Max(If(Value>0, Rolling)),Code,ProdGroup)))"}>} Value) / 3
* ($(vMaxRolling) - Max(Aggr(Max(If(Value>0, Rolling)),Code,ProdGroup))-2)

For example:

The first row of data: Code (6451), product (product1). When Yearmonth is selected from 201810 to 201909, the maximum Yearmonth whose value is greater than 0 is 201906. Summarize the values of 201906 and the previous two months, divide by 3, and multiply by (201909 minus 201906 and then subtract 2). You can also use the rolling value corresponding to Yearmonth to calculate the month.

Expected result: (0 + 0 + 30600) / 3 * (12-9-2) = 10200

Actual result: 0

When Yearmonth is selected from 201810 to 201905.

Expected result: (41376 + 0 + 27110) / 3 * (8-4-2) = 45657.3
Actual Result: 0

Other rows please see the above screenshot.

If the group dimension is drilled down to group or brand, then it should be calculated by group or brand aggregation.

It would be appreciated if you have any suggestions or solutions. 

I have a simple sample as attached. 

[Swang]

Any good idea? 😀

🙏

[MikeW · Accepted solution]

DSEEs can't be used here because DSEEs are expanded ignoring table dimensions, so it will be expanded using the global selections. 

You can try something like below. Not sure what you mean by drilling down to group or band though. 

SUM(
    IF(
        [Rolling] - AGGR(MAX({<[Value]-={0}>} TOTAL<[Code], [Product]> [Rolling]), [Code], [Product], [Group], [Brand], [Rolling]) <= 0
        AND 
        [Rolling] - AGGR(MAX({<[Value]-={0}>} TOTAL<[Code], [Product]> [Rolling]), [Code], [Product], [Group], [Brand], [Rolling]) >- 3,
        [Value],
        0
    )
) / 3 * RANGEMAX(MAX([Rolling]) - MAX({<[Value]-={0}>}[Rolling]) - 2, 0)

[Swang]

Thanks for you suggestion. I will try it.

Please ignore  the description of drilling down to group or band. It's not a problem.

[Swang]

I try this expression and it is OK most of the time, but when I drill down to the Group dimension, one of the rows does not calculate correctly. The correct result is (0+16569+ 66,780)= 83,349, but the actual result is 1,050. Please see the screenshot below for my selections.

The expression I used is as follows:

SUM( 
IF( [Rolling] - AGGR(MAX({<[Value]-={0}>} TOTAL<[Code], [ProdGroup]> [Rolling]), [Code], [ProdGroup], [Rolling]) <= 0

AND [Rolling] - AGGR(MAX({<[Value]-={0}>} TOTAL<[Code], [ProdGroup]> [Rolling]), [Code], [ProdGroup], [Rolling]) >- 3, [Value], 0 
) 
)
/ 3 
* RANGEMAX($(vMaxRolling) - MAX({<[Value]-={0}>}[Rolling]) - 2, 0)

"ProdGroup" is a group dimension(Group/Brand/Product) and "VMaxRolloing" is a variable whose formula is: vMaxRolloing=Max(Rolling).

Any Suggestions? 

Thanks.

[MikeW]

A group is not a real field so it can't be used where the expression is expecting a field. I don't think you can have one expression that works with all the fields in the cyclic group. You might want to consider using the data model to help with doing this.

[Swang]

Maybe you are right. Thanks for your suggestions. 

[Swang · Accepted solution]

I fixed the question. I modified the data model and ran the complex calcualtion logic in load script.