Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
|---|
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)
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.
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.
