Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all,
I searched the community but I didn't get a satisfying answer.
I have a simple straight table with 3 rows:
- Amount previous year
- Amount current year
- The third row should only sum those Parts (Dimension PartID), which have a value >0 in previous AND current year.
I have an if-Clause to do this, which works correctly on dimension-base, but the TOTAL is wrong (2.947.990,80). When you sum up the correct Part-Values, you only get 2.808.454,80
The field "AcceptedAmount" in the Expression is based on single DocumentNumbers (DocNo) and Positions (PosNo) in the table XI_PurchFacts and aggregated on Parts (PartID as Dimension)
I already tried some aggr(sum)-Expressions, but the Total is still wrong.
Sample data attached. Any advice is appreciated 🙂
Try this
Sum(Aggr(
IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0,
Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)
, PartID))
Try this
Sum(Aggr(
IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0,
Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)
, PartID))
Perfect Sunny. Thank you very much!!!
In fact, since this is a straight table... you can continue to use this
IF(Sum({<MainDateYear={$(=only(MainDateYear)-1)}>} AcceptedAmount) <>0 and Sum({<MainDateYear={$(=only(MainDateYear))}>} AcceptedAmount) <> 0,
Sum({<MainDateYear={$(=only(MainDateYear)-1)}>}AcceptedAmount),0)
but change the total mode to Sum of Rows
That's right, but when I change the Layout to Pivot, then I need your right answer.