Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
During load,
LOAD ...
Purchasedate,
Year(Purchasedate) As Purchaseyear,
...
Then in the front end:
Straight table:
Dimension Purchaseyear
Expression Sum(Cost)
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
is there a solution without manipulating the script?
Straight table:
Dimension Year
Expression Sum(Cost)
what i have is the date with its cost for each date
and i want the total sum for each year
During load,
LOAD ...
Purchasedate,
Year(Purchasedate) As Purchaseyear,
...
Then in the front end:
Straight table:
Dimension Purchaseyear
Expression Sum(Cost)
is it possible to do this without manipulating the script?
some expression like
=sum(TOTAL({<Year(Purchasedate))>} Cost)
unfortunetely this expression is not working.
oh ok i forgot to delete Customer ID in the dimension. now it is working !