topic Re: sum up cost per year in QlikView

sum up cost per year

thanhphongle — Wed, 17 Jun 2015 08:07:45 GMT

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?

Re: sum up cost per year

alexandros17 — Wed, 17 Jun 2015 08:47:46 GMT

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

Re: sum up cost per year

thanhphongle — Wed, 17 Jun 2015 08:56:48 GMT

is there a solution without manipulating the script?

Re: sum up cost per year

jonathandienst — Wed, 17 Jun 2015 09:17:48 GMT

Straight table:

Dimension Year

Expression Sum(Cost)

Re: sum up cost per year

thanhphongle — Wed, 17 Jun 2015 09:34:40 GMT

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

and i want the total sum for each year

Re: sum up cost per year

jonathandienst — Wed, 17 Jun 2015 11:31:01 GMT

During load,

LOAD ...

Purchasedate,

Year(Purchasedate) As Purchaseyear,

...

Then in the front end:

Straight table:

Dimension Purchaseyear

Expression Sum(Cost)

Re: sum up cost per year

thanhphongle — Wed, 17 Jun 2015 11:40:56 GMT

is it possible to do this without manipulating the script?

some expression like

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

unfortunetely this expression is not working.

Re: sum up cost per year

thanhphongle — Wed, 17 Jun 2015 11:48:15 GMT

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