14 Replies Latest reply: Jun 6, 2017 1:58 AM by Silambarasan Manickam

# Last Closing Balance SUM Required

Dear Experts,

I have a table with date and segment wise balances.

Now I want to get total of all balances for the segment's last balance at any month closing.

For Example:

Date              Segment          Balance

01-JAN-2017    A                     10

02-JAN-2017    A                      11

10-JAN-2017    A                       12

31-JAN-2017    A                        13

15-JAN-2017    B                         20

31-JAN-2017    B                         25

Now I want to get JAN Balances

A= 13 (As it is last closing balance)

B = 25 (As it is last closing balance)

For total Jan Balance It should be 13=25=38

Regards,

Zahid Rahim

• ###### Re: Last Closing Balance SUM Required

Hello Friend,

Try this formula in table:

=aggr(sum(Balance),Segment,Month)

Regards

Jacek.

• ###### Re: Last Closing Balance SUM Required

Hi Zahid,

in SCRIPT create flag field that flags your max date for each month and segment:

YourTable:

Date,

Segment,

Balance,

Month(Date#(Date,'DD-MMM-YYYY'))      as Month

FROM YourSource;

Left Join (YourTable)

tmp:

max(Date)                                              as MaxDate,

Segment,

Month

Resident YourTable

Group by Segment, Month;

FinalTable:

if(MaxDate=Date,1,0) as @MaxDate

Resident tmp;

DROP Table YourTable;

DROP Field MaxDate;

in FRONT-END:

Create straight table

Dimensions: Month, Segment

Expression: sum({<@MaxDate = {1}>}Balance)

This is performance friendly solution.

BR

Martin

• ###### Re: Last Closing Balance SUM Required

Dear Martin,

I am using qvd tables and letting them join automatically. Do i need to write this further code for a temp table. I am sorry as very much new to this syntax.

Regards,

Zahid Rahim

• ###### Re: Last Closing Balance SUM Required

Hi Zahid,

the above mentioned solution is ready to be used when you have "real" joined table (not associated) in Qlik. Could you please provide your script to find solid solution for you?

Thank you.

BR

Martin

• ###### Re: Last Closing Balance SUM Required

What is the difference between real and associated join?

• ###### Re: Last Closing Balance SUM Required

Hi,

Sum(Aggr(FirstSortedValue(Balance,-Date),Segment))

You can also use in Text Box.

Regards,

Antonio

• ###### Re: Last Closing Balance SUM Required

Dear Antonio,

Thank you very much for the solution.

Can you please guide me for month wise balance as you gave segment wise.

Jan-17 = 38

Suppose we have other months data as well.

Feb-17  = 48

Mar-17   = 58

etc.

Regards,

Zahid Rahim

• ###### Re: Last Closing Balance SUM Required

I added Feb 2017 (48) and Mar 2017 (48)

LOAD *,MonthName(Date) as Month Inline [
Date Segment Balance
01-JAN-2017 A 10
02-JAN-2017 A 11
10-JAN-2017 A 12
31-JAN-2017 A 13
15-JAN-2017 B 20
31-JAN-2017 B 25
01-FEB-2017 A 48
02-MAR-2017 A 58
]
(delimiter is
spaces);

Sum(Aggr(FirstSortedValue(Balance,-Date),Segment,Month))

Regards,

Antonio

• ###### Re: Last Closing Balance SUM Required

Dear Antonio,

Sorry for disturbing you. Cannot i get monthwise last balances only. I mean without segments.

Regards,

Zahid Rahim

• ###### Re: Last Closing Balance SUM Required

Like this ?

• ###### Re: Last Closing Balance SUM Required

or this

or this

• ###### Re: Last Closing Balance SUM Required

Like This:

jan 2017     38

feb2017     48

mar2017     58

• ###### Re: Last Closing Balance SUM Required

Sum(Aggr(FirstSortedValue(Balance,-Date),Segment,Month))

• ###### Re: Last Closing Balance SUM Required

Hi,

You can try below mentioned,

Now I want to get JAN Balances

A= 13 (As it is last closing balance)

B = 25 (As it is last closing balance),

Ans : Sum(Aggr(FirstSortedValue(Balance,-Date),Segment))

For total Jan Balance It should be 13=25=38,

Ans : Sum(Aggr(RangeSum(Above(Sum(Balance),0,RowNo(Total))),Segment,Date))