topic Re: Percentage not matching in QlikView

Percentage not matching

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

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

Re: Percentage not matching

sunny_talwar — Mon, 19 Dec 2016 20:41:34 GMT

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)

Re: Percentage not matching

dandaanilreddy — Mon, 19 Dec 2016 21:01:14 GMT

Thanks Bro. It worked.

Thanks

Anil

Re: Percentage not matching

dandaanilreddy — Mon, 19 Dec 2016 21:19:40 GMT

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

Re: Percentage not matching

sunny_talwar — Mon, 19 Dec 2016 22:19:53 GMT

Here you go

Re: Percentage not matching

dandaanilreddy — Mon, 19 Dec 2016 22:22:59 GMT

Thanks Bro. You are awesome

Thanks

Anil