Total missing in Pivot Report

[mohdhaniff]

Hi,

I'm having problem how to get Total in expression for 'Achievement'. : -

Column 1 : December

sum({$<[Date Disbursed - Month]={'$(=max([Date Disbursed - Month]))'}>} [ETP Amount]))

Column 2 : Achievement

column (1)   /
if(index([Make Segment II],'New Nation')>0,120000000,
if(index([Make Segment II],'New Foreig')>0,120000000,
if(index([Make Segment II],'Used')>0,120000000,
if(index([Make Segment II],'Recondi')>0,40000000,
if(index([Make Segment II],'Other')>0,0,
if(index([Make Segment II],'Enterpr')>0,0,0))))))

Expected Result

Actual Result:

[adamdavi3s]

Without your QVW it is hard to advise exactly, however in this case it is obvious that your total isn't producing as you're indexing and there is no index for the total row.

I guess this might work, just putting a figure in the 'else' section

column (1)  /
if(index([Make Segment II],'New Nation')>0,120000000,
if(index([Make Segment II],'New Foreig')>0,120000000,
if(index([Make Segment II],'Used')>0,120000000,
if(index([Make Segment II],'Recondi')>0,40000000,
if(index([Make Segment II],'Other')>0,0,
if(index([Make Segment II],'Enterpr')>0,0,14586126.34))))))

Otherwise take a look at the dimensionallity function which will allow you to set a separate formula for the total row.

If you can do it as a straight table rather than a pivot, then changing the total mode of the expression (on expression tab) *might* get you what you need?

[Anonymous]

Hi haniff harun,

Close your QVW file and reopen .

Thanks,

Krishna

[adamdavi3s]

I believe the error you are referring to is when the total row does not show up at all.

Clearly in the image above, the total is there it is just not calculating.

[mohdhaniff]

Hi adam,

I got the same result for straight tables.

[mohdhaniff]

Hi Krishna.

still got the same result.

[mohdhaniff]

Yes Adam.

QlikView giving me null result.

Maybe I need to put additional command into the formula. I need help on this command.

[adamdavi3s]

Did you try using dimensionallity?

[Gysbert_Wassenaar]

You add specific amounts based on the segment: if(index([Make Segment II],'New Nation')>0,120000000,

But what amount should be used for calculating the total?

sum({$<[Date Disbursed - Month]={'$(=max([Date Disbursed - Month]))'}>} [ETP Amount]))/ ???? = 19%

How should the unknown amount ???? be calculated?

[mohdhaniff]

Hi Adam,

I already upload a sample data.

Load Data:

LOAD Serial,

     [Make Segment II],

     [ETP Amount]

FROM

(ooxml, embedded labels, table is [QV Sample]);

Expression

Column 1:

=sum([ETP Amount])

Column 2:

=

column (1)  / 
if(index([Make Segment II],'New Nation')>0,120000000,
if(index([Make Segment II],'New Foreig')>0,120000000,
if(index([Make Segment II],'Used')>0,120000000,
if(index([Make Segment II],'Recondi')>0,40000000,
if(index([Make Segment II],'Other')>0,0,
if(index([Make Segment II],'Enterpr')>0,0,0))))))

Result: