Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
Highlighted
Partner
Partner

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

14 Replies
Highlighted
jaceknoden
Contributor

Re: Last Closing Balance SUM Required

Hello Friend,

Try this formula in table:

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

Regards

Jacek.

Highlighted
mato32188
Contributor III

Re: Last Closing Balance SUM Required

Hi Zahid,

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

YourTable:

Load

        Date,

        Segment,

        Balance,

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

FROM YourSource;

Left Join (YourTable)

tmp:

Load

        max(Date)                                              as MaxDate,

        Segment,

        Month

Resident YourTable

Group by Segment, Month;

FinalTable:

LOAD *,

        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

Highlighted
Partner
Partner

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

Highlighted
antoniotiman
Honored Contributor III

Re: Last Closing Balance SUM Required

Hi,

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

You can also use in Text Box.

Regards,

Antonio

Highlighted
Partner
Partner

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

Highlighted
antoniotiman
Honored Contributor III

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

Highlighted
Partner
Partner

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

Highlighted
antoniotiman
Honored Contributor III

Re: Last Closing Balance SUM Required

Like this ?

Highlighted
antoniotiman
Honored Contributor III

Re: Last Closing Balance SUM Required

or this

or this