Pivot table total missing

[NickHoff]

Hello,

I have a pivot table which has the following three dimensions:

BenchmarkMSPickDesc  - Built on an inline table used for a pick statement in expressions

BenchmarkPickMedicalSurgical:

LOAD * INLINE [

BenchmarkMSPickID,BenchmarkMSPickDesc

1, M

2, S

];

BenchmarkObsFactorsStratified

IPClassificationDesc - Built on an inline table used for a pick statement in expressions

PickIPClassification:

LOAD * INLINE [

IPClassificationID, IPClassificationDesc

1, 'Jan - Jun 2014 One Day Stays'

2, 'With 2 Midnights or Greater Based on Time Stamps'

3, 'At Risk One Day Stays'

];

I have the following 4 expressions, which are built with set analysis using alternate states:

IF(BenchmarkMSPickDesc = 'M',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}, BenchmarkMidnightClassification = {'2 Midnights or Greater'}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}, BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkCases)),

IF(BenchmarkMSPickDesc = 'S',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1}, BenchmarkMidnightClassification = {'2 Midnights or Greater'}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1}, BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkCases))))

IF(BenchmarkMSPickDesc = 'M',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}>}BenchmarkTotalBilledCharges),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>}BenchmarkTotalBilledCharges),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkTotalBilledCharges)),

IF(BenchmarkMSPickDesc = 'S',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1}>}BenchmarkTotalBilledCharges),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>}BenchmarkTotalBilledCharges),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkTotalBilledCharges))))

IF(BenchmarkMSPickDesc = 'M',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}>} BenchmarkHybridNetRevAmt),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>} BenchmarkHybridNetRevAmt),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>} BenchmarkHybridNetRevAmt)),

IF(BenchmarkMSPickDesc = 'S',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1}>} BenchmarkHybridNetRevAmt),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>} BenchmarkHybridNetRevAmt),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>} BenchmarkHybridNetRevAmt))))

IF(BenchmarkMSPickDesc = 'M',PICK(IPClassificationID,

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1}>} BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>} BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'2 Midnights or Greater'}>}BenchmarkCases),

SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>} BenchmarkHybridNetRevAmt)/SUM({BenchMark<BenchmarkIPMedicalSurgicalIND={M,S},@BenchmarkInpatientIND={1},BenchmarkMidnightClassification = {'At Risk One Day Stays'}>}BenchmarkCases)),

I believe since i'm using an if statement for each expressions it's not allowing me to select grand totals, although sub totals are allowed.  How can I re-write the logic above to allow a grand total option?

[NickHoff · Accepted solution]

I was able to get the grand totals back by getting rid of my IF statement and tying the inline table to my data model instead of being an island.  In my inline table I just added an additional field named the same as the field int the benchmark data.