topic Re: Total value showing wrong in pivot table in QlikView

Total value showing wrong in pivot table

Anonymous — Wed, 08 Aug 2018 07:06:56 GMT

Hi,

I am calculating sum of sales value, the total showing in the pivot table is different from the total in excel. Sales values are both positive and negative.

For eg; the total i got from pivot table is 35971.85 but the same calculated in excel showing 36033.07.

Re: Total value showing wrong in pivot table

tamilarasu — Wed, 08 Aug 2018 08:16:15 GMT

Hi Remya,

Could you attach a sample file?

Re: Total value showing wrong in pivot table

sibin_jacob — Wed, 08 Aug 2018 08:21:54 GMT

it may be because of your Dimension, there may be some null values in the data.

Go to Dimension tab-> then uncheck supress when value is null

Re: Total value showing wrong in pivot table

Anonymous — Wed, 08 Aug 2018 08:24:31 GMT

Hi,

The attached file is downloaded from qlik.

Re: Total value showing wrong in pivot table

tamilarasu — Wed, 08 Aug 2018 08:28:04 GMT

Try like this as your expression,

Sum(Aggr(Sum(Sales), Region, RouteCode))

Re: Total value showing wrong in pivot table

Anonymous — Wed, 08 Aug 2018 08:30:41 GMT

Actually this is in a self service report, so the dimension will change.

This is the expression am using for sales value

Sum({$<DateKey={">=$(=Date(vFromDate,'YYYYMMDD'))<=$(=Date(vToDate,'YYYYMMDD'))"}, Year=, Quarter=, Month=, WeekName=, Date=>} $(eSales))

Sum({$<DateKey={">=$(=Date(vFromDate,'YYYYMMDD'))<=$(=Date(vToDate,'YYYYMMDD'))"}, Year=, Quarter=, Month=, WeekName=, Date=>} $(eRent))

Re: Total value showing wrong in pivot table

tamilarasu — Wed, 08 Aug 2018 08:37:35 GMT

Remya Sujith wrote:

Actually this is in a self service report, so the dimension will change.

Is that mean cyclic group or variables? Is it possible to attach your qvw file here?

Preparing examples for Upload - Reduction and Data Scrambling

Re: Total value showing wrong in pivot table

Anonymous — Wed, 08 Aug 2018 08:41:18 GMT

Sorry I cannot attach.

It is like the dimension can be chosen by the user, they can select route and sales value or item and sales value or customer and sales value for a range of from date and to date.

Re: Total value showing wrong in pivot table

tamilarasu — Wed, 08 Aug 2018 08:47:10 GMT

Remya Sujith wrote:

It is like the dimension can be chosen by the user, they can select route and sales value or item and sales value or customer and sales value for a range of from date and to date.

Sorry. I just want to know how the dimensions are defined. So that we can include the same in Aggr function.

Re: Total value showing wrong in pivot table

Anonymous — Wed, 08 Aug 2018 09:06:15 GMT

Some of the sub totals are wrong, and the total is calculating by adding those subtotals. If I calculate with values it will give the correct values.

Re: Total value showing wrong in pivot table

tamilarasu — Wed, 08 Aug 2018 09:15:06 GMT

If you can create one sample file and attach here, you might get a solution easily. It is difficult to answer your question by just seeing the values.