Qlikview pivot table - Cumulative calculation without using Above() and Rangesum() functions

[phoenix]

I have the attached summary loan data consisting of originated month, total originated $ in the month and total cumulative Charge off $ from month to month. expected output is the second table where cumulative loss rate for the entire loan Vintage is shown. I have developed this pivot using above() and rangesum() functions but when I convert the pivot table to a line chart, the above() and rangesum() wont retain their functionality and I see completely different %'s. It would be soo easy to convert these pivot into graphs instead of manually exporting the pivot to excel and graphing

[balabhaskarqlik]

May be try your expression like this:

Dimension

Month

Expression as Below

RangeSum(Above(COUNT(Product),0,RowNo()))/RangeSum(Above(COUNT({<Product = , Month = {'>=$(=Min(Month))<=$(=Max(Month))'}>}Product),0,RowNo()))

Or

aggr(rangesum(above(Count([ID ordine]),0,RowNo()))/sum(total <[$Production Year]> Qty),([$Production Year],(NUMERIC, ASCENDING)),([Claim Year],(NUMERIC, ASCENDING)))

[sunny_talwar]

Would you be able to share the app where you have already done this in a table? It might be easy to see what you have and then help you convert this into a line chart in the same app

Best,

Sunny

[phoenix]

Attached QVW to the original post. thanks

[sunny_talwar · Accepted solution]

Try this

Aggr(if(TOB_calculated <= MAX_TOB_VINTAGE_YR,

if(MAX_LOAN_VINTAGE_MTH>=12 or TOB_calculated=0,

      (sum(crg_off_ltd_amt_mod)/SUM (ORG_PRIN_AMT)),

      if(rank(-if(MAX_LOAN_VINTAGE_MTH<12,TOB_calculated))=1,

          ( above(sum(crg_off_ltd_amt_mod)/SUM (ORG_PRIN_AMT))

                                  + 

          (sum(crg_off_ltd_amt_mod)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(crg_off_ltd_amt_mod), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, crg_off_ltd_amt_mod)))))/SUM (ORG_PRIN_AMT)),

          RangeSum(Above(Column(1)),(sum(crg_off_ltd_amt_mod)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(crg_off_ltd_amt_mod), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, crg_off_ltd_amt_mod)))))/SUM (ORG_PRIN_AMT)))

        ),

null()), LOAN_VINTAGE_YR, TOB_calculated)

[phoenix]

That works thanks

[phoenix]

stalwar1‌ Please take a look at this. Its weird that AGGR() function doesn't work for complete load of data but it works if I load the subset of data. Not sure whats going on here. I have validated the data, it looks good

[sunny_talwar]

Seems like sorting issue to me... do you have QV12 or QV11.2 or before?

[phoenix]

We are on QV12

[sunny_talwar]

Try this

Aggr(if(TOB_calculated <= MAX_TOB_VINTAGE_YR,

if(MAX_LOAN_VINTAGE_MTH>=12 or TOB_calculated=0,

      (sum(crg_off_ltd_amt_mod)/SUM (ORG_PRIN_AMT)),

      if(rank(-if(MAX_LOAN_VINTAGE_MTH<12,TOB_calculated))=1,

          ( above(sum(crg_off_ltd_amt_mod)/SUM (ORG_PRIN_AMT))

                                  +

          (sum(crg_off_ltd_amt_mod)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(crg_off_ltd_amt_mod), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, crg_off_ltd_amt_mod)))))/SUM (ORG_PRIN_AMT)),

          RangeSum(Above(Column(1)),(sum(crg_off_ltd_amt_mod)-above(if(MAX_LOAN_VINTAGE_MTH>12, sum(crg_off_ltd_amt_mod), sum(if(MAX_TOB_VINTAGE_YR_MTH > TOB_calculated, crg_off_ltd_amt_mod)))))/SUM (ORG_PRIN_AMT)))

        ),

null()), LOAN_VINTAGE_YR, (TOB_calculated, (NUMERIC)))