I have created a measure using Aggregation based on Dimension when i am using this measure to create

3057791

I have create 1 measure using complex aggregation(Aggregation based on Dimension) i am creating Headcount measure using Sum and Last Aggregation

After that i am creating another Measure Year Ago Measure based on this Headcount Measure but this Year Ago measure which i have created is not giving desired result.

Did anyone faced this problem before and Please provide me the solution and Thank in advance.

Thomas Dodds

Headcounts are inventory style - to compare over time you are better off storing snapshots of the counts at regular intervals.  Doing the calculations logically won't give it to you and trying to nest some of the aggr rules (last, first, etc) in timeseries functions will be problematic.

Once you have a snap shot the AGO() works like a charm:  Date, HEADCOUNT, AGO(HEADCOUNT)

3057791

Hi Thomas Thanks for you answer but i am not able to understand correctly can you please explain it in detail.

Thomas Dodds

Use of information drives physical design ... so in your case you want to know a count at a particular point in time AND what it was at a referential point in time past.

To do this you need to take snapshots of the counts as they are - so then you can look back.  It is far to involved to be trying to look back in time to figure out what the count was at any point by starting with history and adding all the hires and removing the fires/transfers on every request.

Here's a good write up from Kimball on the types of fact tables:

http://www.kimballgroup.com/2008/11/fact-tables/

What you would need is a Periodic Snapshot Grain table.

To make this 'thinkable' -- I assume you look at your bank balance occasionally ... but to know what it was a few months ago you look at an old statement.  That statement is a periodic snapshot of your account at that time and you probably get one monthly.   You could take your own weekly snapshot of your account by printing the web page (or getting the ATM receipt) weekly - nonetheless those are snapshots. 

3057791

Hi Thomas,

Thanks you this solution on the Periodic Snapshot Table, but is there any other way to do it from RPD.

Thomas Dodds

Your issue is solved with a physical structure not a logical one.