Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
ariel_klien
Specialist
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

View solution in original post

8 Replies
MK_QSL
MVP
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)

  )))

ariel_klien
Specialist
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

ariel_klien
Specialist
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

ariel_klien
Specialist
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

View solution in original post

ariel_klien
Specialist
Specialist
Author

Thanks Joe!!!

Not applicable

no worries, got what you meant in the end