Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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

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

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

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

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