I'm running TDP workbook 5.0.5.61, when I add a variance column in the pivot below, the variances shown are correct except for a few rows where large amounts are shown when the value should be zero:
Hi Steve,
what database is it? Can you check what is in the other fields' values, are there any specifics for the incorrect rows? Are the correct values truly "Os" or are they shortened?
dan
Hi Dan, database is SQL Server 2016. Not sure what's going on with the incorrect rows. Since the sum column is correct for those rows, not sure why the variance column wouldn't be correct as well. All the correct rows that show zero are truly zero.
Hi Dan, any updates on this issue?
Hi Steve,
sorry for not replying for some time.
I'm still not able to reproduce it. Have you figured out anything specific with your data that could cause it?
Hi Dan, I tried running the report with different parameters but don’t see anything in the data that would be causing the incorrect variance amounts. It seems
to be totally random. In this screen shot, all the non-zero variances should be zero.
Hi Steve,
can you open a case with Quest support so that you are able to send us your data and your pivot? We cannot reproduce it. Thank you
Dan, I just submitted service request #4535644 and attached the .twf file to the case.
Steve
Hi Steve,
we think it must be related to the values here. When you double click the variance fields you'll drilldown to the list of values and all the fields that are connected to some negative values are displayed incorrectly.
Dan, if I double-click on the 80,672,185.27 variance below, the underlying data matches the columns being used to calculate the variance. The variance should
be zero (30,666.25 – 30,666.25) not 80,672,185.27.
Steve
It looks like the problem rows all have multiple underlying values for a particular cell. In cases where there is a single underlying value for each cell the variances are correctly calculated.
Hi Steve,
this is what we see when we export your pivot into Excel instance:
And we get the same results in Excel as in Toad. This occurs for rows that have negative values in drilldowns.
And we don't have your row that ends 0267.TRNS from your example above so we can't test it.
Dan, when I export the pivot to Excel I’m getting the results below (5 columns). How are you getting all the extra columns?
Dan, when I export the TDP pivot to an Excel pivot I’m getting the same results as you. However, it appears that it’s just exporting
the incorrect data in the TDP pivot to the Excel pivot so I don’t think that really proves anything. The underlying results don’t support a variance of $81,242,405.33. The variance amount should be -9904 – (-9904) = 0.
Steve
Hi Steve,
well, if we go back to the query builder:
Can you confirm the data is correct?
If yes, maybe can you try to wrap up the values into something like ABS function so that we get rid of the negative values and try again?
Thanks
Hi Dan, the negative values in results are correct. However, In query builder I tried changing the amount field to a SUM aggregate which did get rid of most of
the negative values in results and the variance column in the pivot looks correct now (all variances are zero) except for the grand total row which is still showing a huge variance of 82,446,679.98. So I think the underlying issue is still there but we’re
masking it somewhat by changing the amount field to a SUM aggregate. Isn’t the variance column calculated by taking “Revenue-Services” pivot column amount less the “A/R Trade” pivot column amount? Not sure I understand why the underlying values in results
being positive or negative would come into play here.
Steve
Hi Steve,
I took your data from the Query Builder, exported into an Excel report and replicated your Pivot into Excel and the data is shown the same way as in Toad:
The column is calculated the same way in Toad as in Excel. We are not sure how variance is calculated.
Hi Dan, I’m seeing that if I copy the query results to Excel and create a pivot table I’m seeing the same large variances that
are showing in TDP. I was thinking this was a TDP issue but same Excel is doing the same thing so it seems to be an Excel issue. In any case, I modified the query to create separate amount fields for revenue, billing and variance and now the variances are
calculating correctly.
Thanks, Steve
We're genuinely celebrating right now!
