topic Re: sum only last value in QlikView

sum only last value

ariel_klien — Tue, 24 Feb 2015 14:22:03 GMT

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

Re: sum only last value

MK_QSL — Tue, 24 Feb 2015 14:49:17 GMT

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)

)))

Re: sum only last value

ariel_klien — Tue, 24 Feb 2015 14:56:46 GMT

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

Ariel

Re: sum only last value

Tue, 24 Feb 2015 15:33:31 GMT

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

Re: sum only last value

ariel_klien — Tue, 24 Feb 2015 16:01:39 GMT

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.

Ariel

Re: sum only last value

ariel_klien — Tue, 24 Feb 2015 16:33:36 GMT

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.

Re: sum only last value

Tue, 24 Feb 2015 16:34:27 GMT

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

Re: sum only last value

ariel_klien — Tue, 24 Feb 2015 16:40:48 GMT

Thanks Joe!!!

Re: sum only last value

Tue, 24 Feb 2015 16:41:31 GMT

no worries, got what you meant in the end