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

[anbu1984 · Accepted solution]

Load * Inline [

Dept,Year,Sales

A,2011,100

A,2012,200

A,2013,300

B,2011,100

B,2012,200

B,2013,300 ];

Dimension: Dept

Expr1: Sum({<Year={2011}>}Sales)

Expr2: Sum({<Year={2012}>}Sales)

Expr3: Sum({<Year={2013}>}Sales)

[Anonymous]

Thank you Anbu. Your solution worked perfectly!!!

Martin,

I have posted 2 images below: 1st as Anbu suggested, solved the problem. AGGR, however did not bring the desired result, which is in the 2nd image. I have included a 3rd image to show how my fact table is. because it is a lil different,(look at the date field) it is not putting the values in 3 separate year fields. I want to learn how to solve this using AGGR. I appreciate your help very much. Please comment.

[anbu1984]

Check this video to change your pivot table to display years in columns

https://www.youtube.com/watch?v=txgHKIoPQRM

[Anonymous]

Hi Anbu, I was able to split sales into 3 years, thanks to the link above. However I have ran into diff issues this time. the image below represents my pivot table: for every department where there are more than 1 vendor, the subtotal is not showing totals for that category. For departments with only 1 vendor, it is showing subtotal (which is pointless). May be you can see what I am doing wrong. I have checked the partial sum box but its not working.

[anbu1984]

You should get sub-total. Can you post sample app