Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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))))))
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?
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.
Hi adam,
I got the same result for straight tables.
Hi Krishna.
still got the same result.
Yes Adam.
QlikView giving me null result.
Maybe I need to put additional command into the formula. I need help on this command.
Did you try using dimensionallity?
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?
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: