Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
shoaib986
Contributor

Perpetual balance of last 12 months

Hi All,

Assume I have data Years, Month, Year-Month and monthly total. I need to generate Running Balance and show in a line chart for last twelve months only dimension being Year-Month. I want to it by calculated dimension or expression and not by Load Script.

Dimension being Year-Month, say if I select 2016-Aug or nothing (max date is 2016-Aug) it shows only balance of last 12 months as bold and underlined below in line chart.

There is a lot on community about running total for items like sales but not for balances.

    

YearsMonthYear-MonthMonthly TotalRunning Balance
2011Apr2011-Apr      25,404.42          25,404.42
2011May2011-May      40,000.00          65,404.42
2011Aug2011-Aug      (33,878.00)          31,526.42
2011Sep2011-Sep      24,828.00          56,354.42
2011Oct2011-Oct      27,659.00          84,013.42
2011Nov2011-Nov    622,985.00        706,998.42
2011Dec2011-Dec    (535,235.00)        171,763.42
2012Jan2012-Jan    605,478.63        777,242.05
2012Feb2012-Feb      (59,846.00)        717,396.05
2012Mar2012-Mar    852,261.00    1,569,657.05
2012Apr2012-Apr    (231,166.28)    1,338,490.77
2012May2012-May    (914,644.77)        423,846.00
2012Jun2012-Jun  1,039,404.50    1,463,250.50
2012Jul2012-Jul    (516,720.00)        946,530.50
2012Aug2012-Aug    (697,555.00)        248,975.50
2012Sep2012-Sep    996,473.83    1,245,449.33
2012Oct2012-Oct(1,280,586.00)        (35,136.67)
2012Nov2012-Nov    176,962.23        141,825.56
2012Dec2012-Dec    108,276.43        250,101.99
2013Jan2013-Jan    556,708.13        806,810.12
2013Feb2013-Feb      (33,359.93)        773,450.19
2013Mar2013-Mar  2,242,339.30    3,015,789.49
2013Apr2013-Apr(2,572,329.93)        443,459.56
2013May2013-May    641,304.16    1,084,763.72
2013Jun2013-Jun    (174,705.01)        910,058.71
2013Jul2013-Jul    (586,326.93)        323,731.78
2013Aug2013-Aug    (135,315.58)        188,416.20
2013Sep2013-Sep    958,848.14    1,147,264.34
2013Oct2013-Oct    (867,631.37)        279,632.97
2013Nov2013-Nov    614,080.88        893,713.85
2013Dec2013-Dec  2,314,386.34    3,208,100.19
2014Jan2014-Jan(2,619,150.91)        588,949.28
2014Feb2014-Feb    (144,468.15)        444,481.13
2014Mar2014-Mar    503,287.58        947,768.71
2014Apr2014-Apr      86,137.75    1,033,906.46
2014May2014-May    463,865.87    1,497,772.33
2014Jun2014-Jun    194,257.58    1,692,029.91
2014Jul2014-Jul    213,899.17    1,905,929.08
2014Aug2014-Aug(1,013,526.27)        892,402.81
2014Sep2014-Sep  1,652,219.75    2,544,622.56
2014Oct2014-Oct      39,757.18    2,584,379.74
2014Nov2014-Nov(1,512,779.20)    1,071,600.54
2014Dec2014-Dec    222,894.58    1,294,495.12
2015Jan2015-Jan    420,897.14    1,715,392.26
2015Feb2015-Feb    (255,912.76)    1,459,479.50
2015Mar2015-Mar    798,877.48    2,258,356.98
2015Apr2015-Apr  1,094,760.95    3,353,117.93
2015May2015-May(2,047,749.50)    1,305,368.43
2015Jun2015-Jun    (241,750.26)    1,063,618.17
2015Jul2015-Jul    216,308.64    1,279,926.81
2015Aug2015-Aug    938,838.14    2,218,764.95
2015Sep2015-Sep      (97,655.82)    2,121,109.13
2015Oct2015-Oct    (919,803.16)    1,201,305.97
2015Nov2015-Nov      (98,464.46)   1,102,841.51
2015Dec2015-Dec      16,226.97    1,119,068.48
2016Jan2016-Jan    (471,800.01)        647,268.47
2016Feb2016-Feb    (306,240.94)        341,027.53
2016Mar2016-Mar    250,778.16        591,805.69
2016Apr2016-Apr    441,462.42    1,033,268.11
2016May2016-May    (481,035.60)        552,232.51
2016Jun2016-Jun    217,556.80        769,789.31
2016Jul2016-Jul    240,403.90    1,010,193.21
2016Aug2016-Aug      (27,901.00)        982,292.21

Thanks

Shoaib

hicjohnwstevedark

1 Solution

Accepted Solutions

Re: Perpetual balance of last 12 months

Try this:

RangeSum(Above(TOTAL Sum({1}[Monthly Total]), 0, RowNo(TOTAL))) * Avg({<[Year-Month] = {"$(='>' & Date(AddMonths(Max([Year-Month]), -12), 'YYYY-MMM') & '<=' & Date(Max([Year-Month]), 'YYYY-MMM'))"}, Year, Month>} 1)

View solution in original post

14 Replies

Re: Perpetual balance of last 12 months

May be this:

RangeSum(Above(TOTAL Sum([Monthly Total], 0, RowNo(TOTAL))) * Avg({<[Month-Year] = {"$(='>=' & Date(AddMonths(Max([Month-Year]), -12), 'YYYY-MMM') & '<=' & Date(Max([Month-Year]), 'YYYY-MMM'))"}>} 1)

Re: Perpetual balance of last 12 months

Here is a corrected expression with a sample

RangeSum(Above(TOTAL Sum([Monthly Total]), 0, RowNo(TOTAL))) * Avg({<[Year-Month] = {"$(='>' & Date(AddMonths(Max([Year-Month]), -12), 'YYYY-MMM') & '<=' & Date(Max([Year-Month]), 'YYYY-MMM'))"}>} 1)


Capture.PNG

shoaib986
Contributor

Re: Perpetual balance of last 12 months

stalwar1‌ Error in expression. I can't figure out the error.

shoaib986
Contributor

Re: Perpetual balance of last 12 months

Actually,above I give simple example. Actually I have daily transactional data and not monthly, so no monthly totals. I have actual data as below. Is this the reason of error? It's showing no data to display in chart.

Date ,Years,Month,Year-Month,

Amount

Re: Perpetual balance of last 12 months

Did you look at the attached sample?

shoaib986
Contributor

Re: Perpetual balance of last 12 months

I am using PE version. How did you load Year-Month field?

shoaib986
Contributor

Re: Perpetual balance of last 12 months

I even tried with above sample data and your formula in separate qvw file but still not working.

Re: Perpetual balance of last 12 months

Script:

Table:

LOAD Years,

     Month,

     Date(MakeDate(SubField([Year-Month], '-', 1), Num(Month(Date#(SubField([Year-Month], '-', 2), 'MMM')))), 'YYYY-MMM') as [Year-Month],

     Num(Num#([Monthly Total], '#,##0.00;(#,##0.00)')) as [Monthly Total]

FROM

[https://community.qlik.com/thread/232302]

(html, codepage is 1252, embedded labels, table is @1);

Line Chart

[Year-Month]

Expression

RangeSum(Above(TOTAL Sum([Monthly Total]), 0, RowNo(TOTAL))) * Avg({<[Year-Month] = {"$(='>' & Date(AddMonths(Max([Year-Month]), -12), 'YYYY-MMM') & '<=' & Date(Max([Year-Month]), 'YYYY-MMM'))"}>} 1)

shoaib986
Contributor

Re: Perpetual balance of last 12 months

Hi Sunny

I appreciate your support. But if i select in Year and Month list, it's showing only one point and not last 12 months balance. Is there anyway to achieve it?

Capture.PNG