Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Subtotal of subtotal....with a twist

Gang - need your expertise on this issue. I need to have each of my "Incumbent Class" segments add up to 100%. As of now, the math divides it up accross the entire field - which i dont want.

'New Work', 'Retain', and 'Share Growth' are all text attributes within my data. Since this is text within the field, i'm having trouble pulling out this calculation as it relates to the example below.

What do you recomment?

Current State

Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
WON$13,565,943.-$9,888,327.-$9,656,199.-$10,323,343.-
LOST$5,569,704.-$5,545,557.-$8,139,290.-$7,256,197.-
Total$9,945,184.-$8,259,788.-$9,002,761.-$9,026,600.-

Desired State

Incumbent_Class"New Work""New Work""Retain""Retain""Share Growth""Share Growth"TotalTotal
ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
WON$13,565,943.71%$9,888,327.64%$9,656,199.54%$10,323,343.59%
LOST$5,569,704.29%$5,545,557.36%$8,139,290.46%$7,256,197.41%
Total$19,135,647.100%$15,433,883.100%$17,795,489.100%$17,579,540.100%

1 Solution

Accepted Solutions
MVP
MVP

Re: Subtotal of subtotal....with a twist

Something like this?

sum(Revenue)/sum(total <Incumbent_Class> Revenue)

9 Replies
Not applicable

Re: Subtotal of subtotal....with a twist

Hi, recently i experienced something like that.

What i did is:

Create a variable like TableName.TotalAnnulizedRev = Sum(TableName.AnnulizedRev)

Then in the chart the expression should be: Sum(TableName.AnnulizedRev) / TableName.TotalAnnulizedRev

And them configure this expression to show data in percentage style.

I'll hope this help you.

Cya.

MVP
MVP

Re: Subtotal of subtotal....with a twist

Something like this?

sum(Revenue)/sum(total <Incumbent_Class> Revenue)

Not applicable

Re: Subtotal of subtotal....with a twist

Woo Hoo! Worked Great, thanks.

Not applicable

Re: Subtotal of subtotal....with a twist

John, one last question and this will close it out. For the "Annualized Revenue," this seems to be throwing me off. Basically I want to take the months of the contract and divide that over the total revenue. In short, "Eval Revenue / Duration (months)" using these Incumbent Classes. Here's what i have...the totals dont add up....wierd.

Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
WON#######21%$9,888,327.17%$9,656,199.12%$10,323,343.14%
PENDING$6,907,328.72%$7,992,175.77%$7,967,419.81%$7,813,350.79%
LOST$5,569,704.7%$5,545,557.6%$8,139,290.7%$7,256,197.7%
Total$7,557,634.100%$8,051,235.100%$8,145,808.100%$8,034,882.100%

=SUM([Eval Revenue]), SUM([Eval Revenue])/div(SUM([Duration (months)]),12))

MVP
MVP

Re: Subtotal of subtotal....with a twist

I don't really follow, but I suspect you want something more like this:

12*sum([Eval Revenue]/[Duration (months)])

Because (A+B)/(C+D) =/= A/C + B/D, and I think you want the latter.

Not applicable

Re: Subtotal of subtotal....with a twist

Close, let me to a better job of framing it up....

Annualized is just the value of the sale / contract years. For example a $12,000 sale over 2 years will have an annualized value of $6,000. My trouble is with the incument class again. How can i pull through only the sales that are categorized like the table below in an annualized format? PS: The 'totals' dont jive either.

Thanks John!

Incumbent_ClassNew WorkRetainShare GrowthTotal
ResultAnnualized RevAnnualized RevAnnualized RevAnnualized Rev
WON$13,565,943.$9,888,327.$9,656,199.$10,323,343.
PENDING$6,907,328.$7,992,175.$7,967,419.$7,813,350.
LOST$5,569,704.$5,545,557.$8,139,290.$7,256,197.
Total$7,557,634.$8,051,235.$8,145,808.$8,034,882.
MVP
MVP

Re: Subtotal of subtotal....with a twist

I still don't follow, and to the extent that I do, my expression still looks right to me.  Taking your example, you have a $12,000 sale over 2 years.  2 years is 24 months, so I assume field [Duration (months)] has 24 in it.  And therefore, if this is the only sale, my expression gives you 12 * $12,000 / 24 = $6,000 as you requested.  What I was trying to say with my post is what do you do if you have a second sale, this one for $6,000 over 3 years?  That one sale is annualized to $2,000.  So is the total supposed to be $8,000 annualized?  If so, then the expression I gave gives that number, 12 * (12000/24 + 6000/36) = 8000.  If you want this split out by incumbent class, and incumbent class is a dimension, then it should split it by incumbent class. 

I can't make any sense of your table without underlying data, particularly when you tell me that the totals aren't right.  It's just random numbers to me.

Not applicable

Re: Subtotal of subtotal....with a twist

Hopefully this helps - here's an example of the raw data and the revised table.

Regards,

Brian

ResultsIncumbent ClassValueAnnualizedDuration (Month)
WONNew Work $       954 $          954 12
LOSTRetain $       985 $          493 24
WONNew Work $     1,000 $          200 60
LOSTRetain $       400 $          200 24
WONRetain $       332 $          166 24
WONNew Work $       100 $          100 12
LOSTShare Growth $       343 $            86 48
WONShare Growth $       234 $            78 36
LOSTNew Work $       343 $            69 60
WONShare Growth $         45 $            45 12

Incumbent_ClassNew WorkNew WorkRetainRetainShare GrowthShare GrowthTotalTotal
ResultAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of TotalAnnualized Rev% of Total
WON$1,254.95%$166.19%$123.59%$1,543.65%
LOST$69.5%$693.81%$86.41%$848.35%
Total$1,323.100%$859.100%$209.100%$2,391.100%
MVP
MVP

Re: Subtotal of subtotal....with a twist

That helped a lot.  Here you go:

Annualized Rev = 12*sum(Value/"Duration (Month)")
% of total = "Annualized Rev"/(12*sum(total <"Incumbent Class"> Value/"Duration (Month)"))

See attached.

Community Browser