topic Qlikview average different from excel average in QlikView

Qlikview average different from excel average

DestinedTale — Thu, 20 May 2021 07:48:27 GMT

Hi,

I have a Qlikview chart with this expression

sum({<Snapshot_Date = P(Calendar_Date), STATE_M={'LYING'}>} CNTR_DWELL_DAYS_Q * CNTR_Q)/sum({<Snapshot_Date = P(Calendar_Date), STATE_M={'LYING'}>} CNTR_Q)

Attached are the data that qlikview compute and the average excel data.

Qlikview computes as 6.67 but in excel is 6.63.

Can anyone help with this? Thank you.

Re: Qlikview average different from excel average

Vegar — Thu, 20 May 2021 06:22:53 GMT

It's hard to pinpoint without access to the excel/QlikView document, but have you considered if it is an rounding error. Could it be that one of your sources have rounded CNTR_Q- values and the other don't. That could affect your total calculation.

Re: Qlikview average different from excel average

DestinedTale — Thu, 20 May 2021 07:49:15 GMT

Hi, i have attached the excel sheet

Initially I suspected that so I try to round it off to 2decimal place in excel but since can't get the figures.

Re: Qlikview average different from excel average

marcus_sommer — Thu, 20 May 2021 09:52:49 GMT

Qlik displayed always distinct dimension-values but there could be n duplicates exists. Further there are different modes available how the totals are calculated what means that the total is usually not the result of the showed rows - especially if the expression contained conditions and/or is a rate-calculation.

This means your approach to compare Qlik results by exporting them to Excel and apply there further calculations is in general problematic and needs always the appropriate considerations which raw-data are loaded/processed in which way and then how the results are calculated and shown.

So it's quite unlikely that there is a calculation error else the result didn't fit to your expectation. If you are sure that in the context in which these data are used the total should display the average of the rows you may able to get it by changing the total-mode or you used an aggr-function like:

avg(aggr(YourExpression, Dim1, Dim2))

whereby Dim1 and Dim2 are placeholder for the dimensionally context in which the aggregation should be applied - in your case probably the date-field.

- Marcus