Help on pivot table totals including set analysis

[david_ze]

Hi All,

I have a problem in calculating totals in pivot table.

The expression is like this:

if (MITX_PERIOD_FLAG='Y', sum(PROJACTTREE_Budget), sum({<Date=, Year=, Quarter=, Month=, month_num=>} Budget_2))

Dimension is 'DOCNO'.

The total in the pivot table is showing only the numbers related to 'PROJACTTREE_Budget' field only.

The same if i'm trying to show this in text box.

I've trying using the aggr function but with no help.

I'll appreciate your propt help.

Best

David

[er_mohit]

Hii have you tried like this

Sum(total Fieldname)

[david_ze]

not working

[er_mohit]

If you talking about sub total then for this you have to enclose your expression in sum() and in presentation tab check on show partial sum for that particular dimension like this

sum(if (MITX_PERIOD_FLAG='Y', sum(PROJACTTREE_Budget), sum({<Date=, Year=,Quarter=, Month=, month_num=>} Budget_2)))

or if you want total on every row then try this

sum(total if (MITX_PERIOD_FLAG='Y', sum(total PROJACTTREE_Budget), sum({<Date=, Year=,Quarter=, Month=, month_num=>} Budget_2)))

[david_ze]

shows 'error in expression' in both forms

[iliyansomlev]

You may try removing the if clause like

sum({<MITX_PERIOD_FLAG={'Y'}>}PROJACTTREE_Budget)

+sum({<Date=, Year=, Quarter=, Month=, month_num=,MITX_PERIOD_FLAG-={'Y'}>} Budget_2)