Percentage not matching

[dandaanilreddy]

Hello Qlik Experts

I have bar chart and a staright table. In my straight table i am calculating percentage of A+B divided by C. Here A is Actuals, B is Projection minus Actuals and C is company plan . According to the bar chart in my attached application the expression is

darkgreenbar + lightgreenbar / blue line.

I have tested the percentages manually by the above formula. The percentage is matching till nov 2016 from dec 2016 to june 2017 i am geeting wrong results. Can someone help me please. Please find the attached qvw.

Thanks

Anil

[sunny_talwar · Accepted solution]

Try this:

=RangeSum(sum(UnitActuals.BillableCasesFromProc),

If(MonthName(max(UnitActuals.ReportedDate))>=MonthName(now())and UnitActuals.ReportedYear >= Year(now()),

if( BillingRegionType = 'Direct',sum(UnitActuals.BillableCasesFromProc)+  sum(UnitActuals.BillableTrend)+(DirectUplift*vDirectTerritorycnt)*(((UnitActuals.ReportedYear*12)+UnitActuals.ReportedMonth) - ((year(today())*12)+month(today()))),

if( BillingRegionType = 'INTL',sum(UnitActuals.BillableCasesFromProc)+  sum(UnitActuals.BillableTrend)+(IntlUplift*vINTLTerritorycnt)*(((UnitActuals.ReportedYear*12)+UnitActuals.ReportedMonth) - ((year(today())*12)+month(today()))),

if(BillingRegionType = 'US Channel',sum(UnitActuals.BillableCasesFromProc)+  sum(UnitActuals.BillableTrend)+(USChnlUplift*vUSChannelTerritorycnt)*(((UnitActuals.ReportedYear*12)+UnitActuals.ReportedMonth) - ((year(today())*12)+month(today()))),

if(BillingRegionType = 'IVF',sum(UnitActuals.BillableCasesFromProc)+  sum(UnitActuals.ReceivedTrend)+(IVFUplift*vIVFTerritorycnt)*(((UnitActuals.ReportedYear*12)+UnitActuals.ReportedMonth) - ((year(today())*12)+month(today()))),

if(MonthName(max(UnitActuals.ReportedDate))>=MonthName(now()),sum(UnitActuals.BillableCasesFromProc)+  sum(UnitActuals.BillableTrend)

+(If(SubStringCount(Concat(DISTINCT BillingRegionType,','),'Direct')>0,(DirectUplift*vDirectTerritorycnt),0)+

If(SubStringCount(Concat(DISTINCT BillingRegionType,','),'INTL')>0,(IntlUplift*vINTLTerritorycnt),0)+

If(SubStringCount(Concat(DISTINCT BillingRegionType,','),'IVF')>0,(IVFUplift*vIVFTerritorycnt),0)+

If(SubStringCount(Concat(DISTINCT BillingRegionType,','),'US Channel')>0,(USChnlUplift*vUSChannelTerritorycnt),0))

*(((UnitActuals.ReportedYear*12)+UnitActuals.ReportedMonth) - ((year(today())*12)+month(today()))),

)))))-sum(UnitActuals.BillableCasesFromProc))

)/ Sum (UnitActuals.BudgetedCases)

[dandaanilreddy]

Thanks Bro. It worked.

Thanks

Anil

[dandaanilreddy]

I have another problem in my app. In the bar chart i have a expression company plan minus projection ie yellow bar if lightgreenbar+darkgreen bar is less than blue line the yellow bar should be displayed. If i select IVF the bar is not not displaying for months April, may and june 2017 . Any help please.

Thanks

Anil

[sunny_talwar]

Here you go

[dandaanilreddy]

Thanks Bro. You are awesome

Thanks

Anil