How to sum sales by year

[Anonymous]

I am creating a straight table similar to the one below. and I want to pre-calculate sales by year for last 3 years. My fact table has transactional sales, one of the fields is date. what function do I use so that I get sum of sales for each year as a field.

Heade

[ecolomer]

Is that you need?

[ecolomer]

or this?

[#4]

You may want to start dipping your feet into Set Analysis:

Here is the syntax if I am understanding correctly

Expression 1 = Sum({<Year={>=$(=Max(Year(DATEFIELD))}>}Sales)

Expression 2 = Sum({<Year={>=$(=Max(Year(DATEFIELD)-1)}>}Sales)

Expression 3 = Sum({<Year={>=$(=Max(Year(DATEFIELD)-2)}>}Sales)

[Anonymous]

Thanks everyone. sorry it was not very clear what I was trying to explain. I am trying to create a straight table where I have probably 50 departments (which is my dimension, the image attached is just a sample). the transaction table has records for period between 2011 and 2013. If i do sum(Total Sales Receipts), it will lump the total sale from 2011 - 2013 in 1 column -- as in image 1 below. I want the sales for each department to be split in 3 diff years as in image below. So what should be my AGGR function, (i guess) to make that happen

   

[anbu1984]

Check this app

[Anonymous]

Anbu, where is the app

[anbu1984]

I am able to see the app. I am attaching again.

[Anonymous]

i can not open the qvw file. can you post the script here

[emkabi646]

An other way to solve it, is to use the aggr-Function. Create a Pivot-Table with the two Dimensions Dept. and Year. The Expression will be:  aggr(Sum(sales), Year, Dept)