Pivot table total missing

NickHoff — Wed, 04 Mar 2015 15:34:35 GMT

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?

Re: Pivot table total missing

NickHoff — Wed, 04 Mar 2015 16:27:50 GMT

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.

topic Re: Pivot table total missing in QlikView

Pivot table total missing

Re: Pivot table total missing