# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for
Did you mean:
Specialist

## sum only last value

Hello all experts

I have in a table fields: Date,  Month, Year, Name,  Balance

I need to show in a line chart the sum(balance) of the period.

In the chart i have date cycle group: Data, Month, Year

If i choose Date - Sum(Balance) do the work!

but in i choose month or year I need to sum the balance of the last date of the period ex.

2013 - will show the sum of balance at Date 31/12/13

2014 - will show the sum of balance at Date 31/12/14

2015 - will show the sum of balance at Date 18/02/15 - the last Date i Have

2014-02 - will show the sum of balance at Date 28/02/2014

2015-01 - will show the sum of balance at Date 31/01/2015

2015-02 - will show the sum of balance at Date 18/02/2015 - the last Date i Have

I attached a file

Does anyone have an idea how to accomplish that?

Ariel

1 Solution

Accepted Solutions
Not applicable

Ah right sorry so you want the change to occur based on your cycle group rather than what you actually select?

See attached think that is what you mean then

Joe

8 Replies
MVP

Use this expression

=

IF(GetFieldSelections(Date),

SUM(Balance),

IF(GetFieldSelections(Year),

SUM({<Date = {"\$(=Max({<Year = {'\$(=max(Year))'}>}Date))"}>}Balance),

IF(LEN(GetFieldSelections(Month))>0,

SUM({<Date = {"\$(=MonthEnd(MakeDate(Left(Month,4),SubField(Month,'-',-1))))"}>}Balance)

)))

Specialist
Author

Hi Manish,

Thanks for the Quick answer, but i'm not looking for 1 value i want the make a compare chart.

I need to compare years.

so, if in my X bar will put Year i will see 2013 2014 2015 but the sum(balance) will sum the 31/12/2013  31/12/2014  18/02/2015

BR

Ariel

Not applicable

Hi Ariel,

please see attached, I think that is giving you the answer you are looking for. Sums all the dates you select single or multiple, or the last in the period for year/month.

hope that helps

Joe

Specialist
Author

Hi Joe.

Thanks for your answer but still i cant compare between years or months it give me 1 value.

If i chose in the group "Date" - Year. i want to see:

in 2013 the sum(Balance) of 31/12/2013

in 2014 the sum(Balance) of 31/12/2014

in 2015 the sum(Balance) of 18/02/2015

now i can only see the balance in 2015.

BR

Ariel

Specialist
Author

Hi,

I found a partial solution:

in the script:

 Table_Temp: LOAD Date,   Year&'-'&num(month(Date)) as Month,      Year,      Name,      Balance FROM .\Balance_Test.xls (biff, embedded labels, table is Sheet1\$); Max_Date: LOAD Max(Date) as Max_T Resident Table_Temp; Let vMaxDate=Peek('Max_T'); Drop Table Max_Date; Table: LOAD *,   if(Date=floor(MonthEnd(Date)) or Date='\$(vMaxDate)',1,0) as Month_End,   if(Date=floor(YearEnd(Date)) or Date='\$(vMaxDate)',1,0) as Year_End Resident Table_Temp; drop Table Table_Temp;

and in the chart:

if(GetCurrentField(Dates)='Date',sum(Balance),

if(GetCurrentField(Dates)='Month',Sum({<Month_End={1}>}Balance),

if(GetCurrentField(Dates)='Year',Sum({<Year_End={1}>}Balance))))

but it is not dynamic.

if i chose the first 15 days of all months it will not show me the correct balance.

Not applicable

Ah right sorry so you want the change to occur based on your cycle group rather than what you actually select?

See attached think that is what you mean then

Joe

Specialist
Author

Thanks Joe!!!

Not applicable

no worries, got what you meant in the end