Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
remyasujith17
Contributor III
Contributor III

Total value showing wrong in pivot table

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.

10 Replies
tamilarasu
Champion
Champion

Hi Remya,

Could you attach a sample file?

sibin_jacob
Creator III
Creator III

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

remyasujith17
Contributor III
Contributor III
Author

Hi,

The attached file is downloaded from qlik.

tamilarasu
Champion
Champion

Try like this as your expression,

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

remyasujith17
Contributor III
Contributor III
Author

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

tamilarasu
Champion
Champion

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

remyasujith17
Contributor III
Contributor III
Author

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.

tamilarasu
Champion
Champion

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.

remyasujith17
Contributor III
Contributor III
Author

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.