Help with aggregating an expression

[hopkinsc]

Hi All.

I have the following expression

sum(aggr($(vAnnualSalesv2),SubCat,SubCat1,[Item Description]))

which is made up of the following

$(vAvgPrice)*$(vForecastSales)+$(vTotalSalestoLastPeriod)

I thought it was working but noticed that the results were lower than expected. On investigation i found that some rows of data were not being included because they didn't have a 'ForecastSales' value (as they have been discontinued).

i have therefore changed the expression to the following..

if(isnull($(vForecastSales)),$(vTotalSalestoLastPeriod),sum(aggr($(vAnnualSales),SubCat,SubCat1,[Item Description])))

this populates a straight table correctly when i am drilling down to 'Product' level, but it doesnt aggregate properley when going back up the drill down.

as you can see, the total for the 3 lines add correctly..

but when going back up yoiu can see that it shows a different value (highlighted - its missing the 7,102.08)

Can anyone see what i am doing wrong?

thanks, example attached

[hopkinsc]

Can anyone help with this?

[sunny_talwar]

Are you sure that they drilled down version is the right number? I used this

sum(aggr(if(isnull($(vForecastSales)),$(vTotalSalestoLastPeriod),$(vAnnualSales)),SubCat,SubCat1,[Item Description]))

Not drilled down version looks almost the same, except one change...

But drilled down now matches with non drilled down version

This isn't right?

[hopkinsc]

Hi Sunny,

Thanks for your reply.

What i need is when drilled down, the top line should display 7102.08, which then changes the total to 134,248.32.

Then going back up a level i should see that total value for that particular subcat1 (Kbd Pxafxq: Uiibdl AT/Pzagix) is showing 681,308.43.

then obviously going up a higher level would also aggregate correctly.

does that make sense?

[sunny_talwar]

How did you come up with this number?

681,308.43

[sunny_talwar · Accepted solution]

May be this

Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item Description]))

[hopkinsc]

Sorry! it should be this..

689,053.47

[hopkinsc]

I think thats it! it looks like one of the others was also wrong which is now correct too.

Thanks Sunny, i will put this into my live doc and see what happens

[sunny_talwar]

Sounds like a plan

Best,

Sunny

[hopkinsc]

OK that seems to work for that expression, are you able to help with getting the same thing to work on some other expressions?

I am just about to create a new discussion..