New to QlikView

Discussion board where members can get started with QlikView.

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

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

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

Tags (4)
1 Solution

Accepted Solutions
Highlighted
MVP

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

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)

15 Replies
Highlighted
Honored Contributor

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

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

Highlighted
MVP

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

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

Highlighted
Contributor

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

Attached QVW to the original post. thanks

Highlighted
MVP

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

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)

Highlighted
Contributor

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

That works thanks

Highlighted
Contributor

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

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

Highlighted
MVP

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

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

Highlighted
Contributor

We are on QV12

Highlighted
MVP

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

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