Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
thanhphongle
Creator II
Creator II

sum up cost per year

hello community,

i have 3 columns

Customer IDCostPurchasedate
CID110100001.01.2011
CID111200001.04.2011
CID112300001.06.2012
CID113100001.05.2013
CID114100001.07.2013
CID115200001.04.2014
CID116300001.12.2014

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

->

YearCost per year
20113000
20123000
20132000
20145000

could anyone give me a solution for that?

1 Solution

Accepted Solutions
jonathandienst

During load,

LOAD ...

     Purchasedate,

     Year(Purchasedate) As Purchaseyear,

     ...

Then in the front end:

Straight table:

Dimension Purchaseyear

Expression Sum(Cost)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

7 Replies
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
Creator II
Creator II
Author

is there a solution without manipulating the script?

jonathandienst

Straight table:

Dimension Year

Expression Sum(Cost)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
thanhphongle
Creator II
Creator II
Author

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

and i want the total sum for each year

jonathandienst

During load,

LOAD ...

     Purchasedate,

     Year(Purchasedate) As Purchaseyear,

     ...

Then in the front end:

Straight table:

Dimension Purchaseyear

Expression Sum(Cost)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

thanhphongle
Creator II
Creator II
Author

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
Creator II
Creator II
Author

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