Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item Description]))
Can anyone help with this?
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?
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?
How did you come up with this number?
681,308.43
May be this
Sum({<Period>}Aggr(If(IsNull($(vForecastSales)), $(vTotalSalestoLastPeriod), $(vAnnualSales)), SubCat, SubCat1, [Item Description]))
Sorry! it should be this..
689,053.47
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
Sounds like a plan
Best,
Sunny
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..