Last Closing Balance SUM Required

[zahidrahim_ocp]

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

[zahidrahim_ocp]

Like This:

jan 2017     38

feb2017     48

mar2017     58

[antoniotiman]

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

[mato32188]

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

[zahidrahim_ocp]

What is the difference between real and associated join?

[Silambarasan1306]

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))