topic Re: Total missing in Pivot Report in QlikView

Total missing in Pivot Report

mohdhaniff — Wed, 25 Nov 2020 16:16:04 GMT

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:

Re: Total missing in Pivot Report

adamdavi3s — Wed, 21 Dec 2016 07:19:28 GMT

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

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?

Re: Total missing in Pivot Report

Anonymous — Wed, 21 Dec 2016 07:32:38 GMT

Hi haniff harun,

Close your QVW file and reopen .

Thanks,

Krishna

Re: Total missing in Pivot Report

adamdavi3s — Wed, 21 Dec 2016 07:41:08 GMT

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.

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 07:47:45 GMT

Hi adam,

I got the same result for straight tables.

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 07:48:32 GMT

Hi Krishna.

still got the same result.

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 07:52:18 GMT

Yes Adam.

QlikView giving me null result.

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

Re: Total missing in Pivot Report

adamdavi3s — Wed, 21 Dec 2016 07:54:34 GMT

Did you try using dimensionallity?

Re: Total missing in Pivot Report

Gysbert_Wassenaar — Wed, 21 Dec 2016 08:25:04 GMT

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?

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 08:31:42 GMT

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:

Result:

Re: Total missing in Pivot Report

adamdavi3s — Wed, 21 Dec 2016 08:41:36 GMT

Ok so as I suggested, if you change the total mode it works, you need to tell us what output you're expecting here

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 08:42:36 GMT

Hi gysbert,

Thanks for your comment. You giving me an idea.

I got the answer... the achievement should be write as below:

(120000000+120000000+120000000+40000000) // Amount targetted only

Re: Total missing in Pivot Report

mohdhaniff — Wed, 21 Dec 2016 09:13:10 GMT

Thanks Adam,

I got it. I Change 'else' value with a value as below

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,120000000+120000000+120000000+40000000))))))