sum only last value

[ariel_klien]

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

[MK_QSL]

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]

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

[#4]

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]

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]

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.

[#7 · Accepted solution]

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

[ariel_klien]

Thanks Joe!!!

[#9]

no worries, got what you meant in the end