YTD and MAT calculations

Question

YTD and MAT calculations

vallisaravanan

SSC Eights!

Points: 914
More actions
June 18, 2010 at 6:32 am

#225784

Hi,
I am trying to calculate YTD and Moving Annual Turnover sales values for the products and store it in the database itself instead of doing it in the cube to minimize runtime processing. The logic works fine if the product has transactions for all the months in all the location. But, if a product say Bikes is not having transaction in a particular state in a particular month, then the YTD values or MAT values are not calculated for that state for that month.So summing up of the MATValues of all locations doesnt give the proper MAT Value for that product for that month. I think I am missing out something. Should we have to calculate the MATValues for all the products for all the locations for all the months irrespective of whether they are having transaction or not? Or is there any other way? Please advise.
Thanks and Regards,
Valli

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

pete.griffiths SSC Veteran Points: 289 More actions · Answer 1

I have to do the same calculations in our business. I opted for doing the calculations in the cube instead of the warehouse. It performs very well.

I think you have two options:

1) Do the calculation in the cube.

2) Change the atomic level calculation to get the last non empty value (By using scoping) and then the Aggregation should work for all levels above.

(The simplest way to do this is to create another measure for each, but use the LastNonEmpty aggregation instead of Sum. Then change the value of the original measure at the lowest level to the value of the new measure by using a scope statement in the cube calculations tab.

I hope that made sense!

vallisaravanan SSC Eights! Points: 914 More actions · Answer 2

Hi,

Thanks pete.griffiths. I am doing it currently in the cube only and it is giving correct results. But since I have many more calculations based on this YTD and MAT calculations, I want to have it done in the data warehouse. I have taken the lastNonempty value and it works fine for the leaf level. But I do not understand what you mean by saying

‘create another measure for each, but use the LastNonEmpty aggregation instead of Sum. Then change the value of the original measure at the lowest level to the value of the new measure by using a scope statement in the cube calculations tab’.

Should we duplicate the values of Mat and YTD in another field and have the aggregation for one as Sum and another as nonempty or have another calculated member defined based on the Mat and YTD and make it use this lastnonempty value when it is at the lowest level of product and location dimension? Will it aggregate the data at the higher levels based on this newly defined calculated member? Please explain as I am not that much an expert in SSAS.

Thanks and Regards,

Valli

pete.griffiths SSC Veteran Points: 289 More actions · Answer 3

Hi Valli,

I think you have understood pretty well, I'll try to clarify:

assuming we have a field in the data warehouse called MAT.

Create two measures using MAT, one using Sum and one using LastNonEmpty, we will call these measures [MAT] (using sum) and [MAT LastNonEmpty].

In your calculations you would need to add something like this:

SCOPE([Measures].[MAT]

,Descendants([Product].[SKU],, LEAVES)

,Descendants([Location].[Location Code],,LEAVES));

this=[MAT LastNonEmpty];

END SCOPE;

Doing this means that at the lowest level you will get the last non empty value but it will use sum to aggregate at all levels above.

I hope the example helps, it will probably not be exactly the correct solution to your business model, but I'm sure it will work with some tinkering.

Having said all that, I think that you really should stick to doing the calculations in the cube. I can imagine that you would show an incorrect MAT if a product hadn't had any movements in a whole year, but you showed the last non empty MAT. It would be a year out of date!

I may be wrong with that assumption but be aware that there will be some gotchas to consider!

The better solution would be to use a shell dimension to do all of the calculations you need.

There is a decent article on it here

Good luck!

Pete