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

[infosense_devel]

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.

[sunny_talwar · Accepted solution]

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

[infosense_devel]

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.

[sunny_talwar]

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

[infosense_devel]

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.

[sunny_talwar]

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

[infosense_devel]

Thanks it working. 🙂