How to sum/aggregate correctly an expression between actual period and before

[#1]

For a sum of actual period the following expressions works fine on but item and aggregated category levels:

(

SUM(  {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >}  Spend_landed_DKK_excl_VAT) 

/

SUM( {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >} Actual_Quantity_Invoiced) 

)

*

SUM( {$< Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number) >} Actual_Quantity_Invoiced) 

Example;

For a sum of a calculation between before and actual period the sum on item level is correct but on category level it is not the "sum" of the items:

(

(

Before(

SUM(

{$<

Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

>}

Spend_landed_DKK_excl_VAT)

/

SUM(

{$<

Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

>}

Actual_Quantity_Invoiced)

)

)

*

SUM(

{$<

Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number)

>}

Actual_Quantity_Invoiced)

)

Example:

Sum Actual to higher level is correct.

Sum Prev to higher level is incorrect; should have been 18.560.770

Any experts who can see my error, maybe I should use aggr somehow?

Best regards,

LL

[sunny_talwar]

How about this:

Sum(Aggr(

(

(

Before(

SUM(

{$<

Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

>}

Spend_landed_DKK_excl_VAT)

/

SUM(

{$<

Item_Number = P({<FLAG_SoldFollowingYear={">0"}>} Item_Number)

>}

Actual_Quantity_Invoiced)

)

)

*

SUM(

{$<

Item_Number = P({<FLAG_SoldPrevYear={">0"}>} Item_Number)

>}

Actual_Quantity_Invoiced)

),

[Category L1], [Sub-category L2], [Sub-category L3], Item_Number, [Order Year]))

Since you are using before function, you might need to sort between the aggr function as well. So look into sorting for aggr function here:The sortable Aggr function is finally here!

[#3]

Hi Sunny,

Thanks for the quick answer.

However, it returns 0-values, I am reading up on the sort part now.

BR

LL

[sunny_talwar]

I think the sort part would be the important part because Before needs to look back and without correct sorting, you will be looking before based on sorting in script, whereas you can configure your sorting using the structured parameter within aggr function

[#5]

Hi Sunny,

Thank you for your advices, I understand the logic of what you mention regarding sorting Before data, and I agree that this could be the reason why it returns zeros.

I am struggling a lot in getting the sorting to work properly for the AGGR looking into the link you have sent, would you any suggestions how the correct setup should be for my case?

[sunny_talwar]

I guess I don't know what is the correct sort order to get the numbers right. If you can let me know the data type (NUMERIC or TEXT) and sort order needed for each of the dimension, I might be able to suggest something. or if possible share a sample.

Best,

Sunny

[#7]

Following are all of data type TEXT: [Category L1], [Sub-category L2], [Sub-category L3], Item_Number

I would say ASC as sort order for all of them.

The [Order Year] I would say is irrelevant, as the Before looks into the last period which is one year only.

The tricky part is that it has to calculate on item_number level each item that existed year before and current year the formula and then be able to aggregate that on higher levels. At item level it works, the aggregation part is where it gets it wrong.