Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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)
)))
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
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
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
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.
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
Thanks Joe!!!
no worries, got what you meant in the end