If you encounter any discrepancies in your KPIs, for instance when you compare the average of a data set in Excel vs Qlik Sense, it is helpful to check if your data consist any duplicate values.
To check if your data has some duplicates you can use a simple Count function and compare the result with a count (distinct) function:
In this example we want to aggregate several timestamps by using an average function in a expression. However when we export the actual Table, for instance in Excel, we receive a different average there as in our KPI.
To analyze what might go wrong, the next step would be to check how many records the data set consist by using the function Count.
As we can see the actual number of distinct numbers in our second expression is smaller than in our first expression indicating that our data contains duplicates which of course affects our KPI average calculation.
We can either use a distinct in our average expression in the frond end or we can modify our data model by avoiding duplicates in the back end in order to display the right calculation.