topic Re: "Invoice YTD vs Budget YTD %" showing wrong result. in QlikView

"Invoice YTD vs Budget YTD %" showing wrong result.

infosense_devel — Mon, 30 May 2016 17:05:52 GMT

Hello all,

i am showing "invoice YTD" , "Budget YTD" and "Invoice YTD vs Budget YTD %" along with other column in one table. but "Invoice YTD vs Budget YTD %" is not showing data as per expectation.

formula for "Invoice YTD vs Budget YTD %":-

("invoice YTD"/"Budget YTD")*100

i have tried with Column(8)/column(7) formula in qlik view. also i have also tried by replacing formula for particular column with column() function but still it is showing wrong data.

my YTD formula areas per below:-

Budget YTD:- =sum({<Common_Month = ,[Common_Day (#)]=,REC_TYPE = {'BudgetRec'}>}DAILY_BUDGET*XRATE_CONST*RATE)

with "Full Accumulation" property.

Invoice YTD:-

=Sum({<Common_Month = ,

REC_TYPE={InvoiceDateRec}>} (RAGLDA_ACCTD_AMOUNT*XRATE_CONST*RATE))

Sum({<Common_Month = ,

REC_TYPE = {'OfflineData'}>}ACCTD_AMOUNT*XRATE_CONST*RATE)

with "Full Accumulation" property.

Thanks in Advance.

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

sunny_talwar — Mon, 30 May 2016 18:26:40 GMT

Try this:

RangeSum(Above(Sum({<Common_Month = , [Common_Day (#)] = , REC_TYPE = {'BudgetRec'}>} DAILY_BUDGET * XRATE_CONST * RATE), 0, RowNo()))

RangeSum(Above(Sum({<Common_Month = ,

REC_TYPE = {InvoiceDateRec}>} (RAGLDA_ACCTD_AMOUNT * XRATE_CONST * RATE))

Sum({<Common_Month = ,

REC_TYPE = {'OfflineData'}>} ACCTD_AMOUNT * XRATE_CONST * RATE), 0, RowNo()))

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

infosense_devel — Tue, 31 May 2016 09:24:40 GMT

Thanks for you reply Sunny,

it working exactly what i want only one problem i am facing and that Grand total, i am not able to get grand total for that particular column in which i have used RangSum().

Please take a look at screenshot.

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

sunny_talwar — Tue, 31 May 2016 12:32:12 GMT

What would you expect to see there? Sum of accumulation or just the final accumulated number? I would think the later, but will wait for your response before I propose a solution

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

infosense_devel — Tue, 31 May 2016 13:10:20 GMT

Hello,

for Budget YTD and Invoice YTD i need final accumulated number but for "Invoice YTD vs Budget YTD %" i need average.

Please find below Screen.

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

sunny_talwar — Tue, 31 May 2016 15:43:52 GMT

For Budget and Invoice continue to use what you are using today (since it is working). For the percentage, you can set total mode to average on the expression tab of the straight table chart properties

Re: "Invoice YTD vs Budget YTD %" showing wrong result.

infosense_devel — Tue, 31 May 2016 17:03:02 GMT

Thanks it working. 🙂