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

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

phoenix — Wed, 25 Nov 2020 16:16:04 GMT

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

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

BalaBhaskar_Qlik — Fri, 03 Aug 2018 06:16:40 GMT

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

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

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

sunny_talwar — Fri, 03 Aug 2018 10:58:06 GMT

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

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

phoenix — Fri, 03 Aug 2018 13:14:36 GMT

Attached QVW to the original post. thanks

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

sunny_talwar — Fri, 03 Aug 2018 13:35:07 GMT

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)

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

phoenix — Fri, 03 Aug 2018 20:59:03 GMT

That works thanks

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

phoenix — Thu, 30 Aug 2018 20:07:48 GMT

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

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

sunny_talwar — Fri, 31 Aug 2018 02:11:48 GMT

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

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

phoenix — Fri, 31 Aug 2018 12:59:05 GMT

We are on QV12

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

sunny_talwar — Fri, 31 Aug 2018 13:01:56 GMT

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

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

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

phoenix — Fri, 31 Aug 2018 13:10:00 GMT

That worked. So this is coming from incorrect data type of TOB_calculated?

Should I change it in the data model while loading ( num#(tob_calculated) as TOB_calculated ) instead of set analysis?

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

sunny_talwar — Fri, 31 Aug 2018 13:11:29 GMT

Not the incorrect data, but the incorrect sorting

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

phoenix — Fri, 31 Aug 2018 13:22:58 GMT

ok. thanks

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

phoenix — Fri, 07 Sep 2018 13:43:49 GMT

stalwar1‌ Hi your suggestion on using (TOB_calculated, (NUMERIC))) worked last time. But with the same expression, this time curves started spiking up. Not sure if this is coming from same incorrect sort order problem within the application. Please take a look. Thanks

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

sunny_talwar — Fri, 07 Sep 2018 13:57:06 GMT

Siva - it would be easier if you open a new thread for this, this way you get more set of eyes on this. Also, I suggest you to share an updated app where we can actually see the problem.

Thanks,

Sunny

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

phoenix — Mon, 10 Sep 2018 15:15:25 GMT

Agree! I have looked at the data again and looks like I was missing some data. Fixed it and the graph looks good now. Appreciate your help