sum up cost per year

[thanhphongle]

hello community,

i have 3 columns

Customer ID	Cost	Purchasedate
CID110	1000	01.01.2011
CID111	2000	01.04.2011
CID112	3000	01.06.2012
CID113	1000	01.05.2013
CID114	1000	01.07.2013
CID115	2000	01.04.2014
CID116	3000	01.12.2014

What I would like to do is to sum up the cost for every year.

->

Year	Cost per year
2011	3000
2012	3000
2013	2000
2014	5000

could anyone give me a solution for that?

[alexandros17]

This is what you need step by step

MyTable:
LOAD * Inline [
CustomerID, Cost, Purchasedate
CID110, 1000, 01.01.2011
CID111, 2000, 01.04.2011
CID112, 3000, 01.06.2012
CID113, 1000, 01.05.2013
CID114, 1000, 01.07.2013
CID115, 2000, 01.04.2014
CID116, 3000, 01.12.2014
];

Temp:
NoConcatenate
LOAD CustomerID, Cost, Purchasedate, Right(Purchasedate,4) as Year Resident MyTable;
DROP Table MyTable;

CostPerYear:
LOAD Year, Sum(Cost) as CostYear Resident Temp Group By Year;

Hope this helps

[thanhphongle]

is there a solution without manipulating the script?

[jonathandienst]

Straight table:

Dimension Year

Expression Sum(Cost)

[thanhphongle]

what i have is the date with its cost for each date

and i want the total sum for each year

[jonathandienst · Accepted solution]

During load,

LOAD ...

     Purchasedate,

     Year(Purchasedate) As Purchaseyear,

     ...

Then in the front end:

Straight table:

Dimension Purchaseyear

Expression Sum(Cost)

[thanhphongle]

is  it possible to do this without manipulating the script?

some expression like

=sum(TOTAL({<Year(Purchasedate))>} Cost)

unfortunetely this expression is not working.

[thanhphongle]

oh ok i forgot to delete Customer ID in the dimension. now it is working !