topic Re: Pivot Table totals are wrong in QlikView

Pivot Table totals are wrong

Mon, 01 Sep 2014 15:37:11 GMT

Im trying to create a fairly simple table which based on the dimension "Income/Cost", and 3 expressions "YTD income", "YTD cost", "Net". This works for each row, but the table totals arent right (its adding the costs & income together). An example of the result is below:

Activity Income/Cost YTD Income YTD Cost Net

AAEM01 Income £28,621 0 £28,621

Cost £0 £65,284 -£65,284

TOTAL £93,905 £93,905 £0

The expression to calculate YTD income is:

If(Income/Cost = 'Cost',0,sum( [YTD Total] ))

Ultimately, I want to remove the Income/Cost dimension to just show a total for each activity.

Any thoughts/solutions much appreciated.

Many thanks in advance

Re: Pivot Table totals are wrong

Anonymous — Mon, 01 Sep 2014 15:41:17 GMT

try using

if (Income/Cost='Cost',sum(total <Income/Cost> [YTD Total])

how is the definition of [YTD Total]?

Re: Pivot Table totals are wrong

Mon, 01 Sep 2014 15:54:13 GMT

Thanks for the quick reply.

The total has changed, but to £0

YTD Total is my import data by cost code, e.g. staff cost, travel, accomodation, printing, phones, etc etc

Re: Pivot Table totals are wrong

Anonymous — Mon, 01 Sep 2014 16:14:22 GMT

sorry, thats true, in the total row there is no income/cost

if you remove the Income/Cost column you won't get the result as stated in your desired table

alternatively you can use an own column with sum(YTD Income)+sum(YTD Cost) and you have the total number

Re: Pivot Table totals are wrong

Mon, 01 Sep 2014 16:21:14 GMT

Try the following:

1 Creates a pivot table.

2 In the expression stated

sum ([YTD Total])

3 in one dimension adds the column "Income / Cost"

4 Add the dimension visually on the expression in the table, as shown in the picture

5. tab filing, adds the option to display totals

Re: Pivot Table totals are wrong

vijay_iitkgp — Mon, 01 Sep 2014 17:00:01 GMT

I think if Income / Cost is associated with Data then you can simply use the expression:

Sum(If(Income/Cost = 'Cost',0,( [YTD Total] ))) for Income

Re: Pivot Table totals are wrong

Tue, 02 Sep 2014 07:52:16 GMT

Thanks, yes this works

Re: Pivot Table totals are wrong

Tue, 02 Sep 2014 07:54:27 GMT

Thank you