Incorrect total percentage in Table chart

[SarahAzizan]

Dear Experts,

I have a column Completion in table with some calculation. It is showing correct individual values but the total is wrong. 

 

Archival data #DOC = count( {$<SET={'Archival data'}>}distinct DOCNO)

Current data #DOC = count({$<SET={'Current data'}>}distinct DOCNO)

Completion = count({$<SET={'Current data'}>}distinct DOCNO) / count( {$<SET={'Archival data'}>}distinct DOCNO)

Completion column number formatting is Number 12.3%

The correct Total Completion % should be 62798 / 71722 = 0.8755 or 87.6%

Regards,

Sarah Azizan

[Nagaraju_KCS]

try this 

= count({<SET={'Current data'}>} distinct DOCNO)
/
count({<SET={'Archival data'}>} TOTAL distinct DOCNO)

[Chanty4u]

Try this 

Sum(

    Aggr( Count({$<SET={'Current data'}>} DISTINCT DOCNO), DOCNO )

)

/ 

Sum(

    Aggr( Count({$<SET={'Archival data'}>} DISTINCT DOCNO), DOCN

O )

)

[Vegar]

Are you sure that the row total of Archival data #DOC and Current data #DOC is correct? What is the setting for totalling in your table? Is it the expression total or is it sum of rows? The sum of rows totalling can be "wrong" in case you have dimensions in your table that share DOCNO with each other.

Try having your expression outside of this table, in a separate table with no dimensions. Is it correct then?

[SarahAzizan]

The individual Completion value is not correct & Total Completion is the same 57.9% 

[SarahAzizan]

The Completion value become all 0.00%

[Nagaraju_KCS]

try this

SUM(Aggr(count( {$<SET={'Archival data'}>}distinct DOCNO), [Company Code], [Company Name]))

/

SUM(Aggr(count({$<SET={'Current data'}>}distinct DOCNO), [Company Code], [Company Name]))

[SarahAzizan]

The setting for totalling in the table is Auto.

Ya, looks like the value of the expression with no dimensions are different.

 

[marcus_sommer · Accepted solution]

Only by using simple aggregations like sum(Field) or count(Field) are the totals a no brainer. As far as any conditions are applied and/or rates calculated and/or distinct is used and similar stuff the calculation depends on the context to the data-set + data-model + object-dimensionality - for example the conditions may be valid on the object row-level but not on the totals or there are overlapping DOCNO between the companies which will result in different numbers between the rows and the total - various possibilities ...

The general solution were already hinted and is to wrap the base-calculation with an appropriate aggr() construct. The dimensions for the aggr() are usually the object-dimensions but it may differ. Helpful is often to use a table-box with all relevant fields and investigate the existing relationship there (against an appropriate reduced data-set - means just a few rows but resulting in unexpected results).

[SarahAzizan · Accepted solution]

Found the correct expression:

Completion = SUM(Aggr(count({$<SET={'Current data'}>}distinct DOCNO), [COMPANY_CODE] ))
/ SUM(Aggr(count( {$<SET={'Archival data'}>}distinct DOCNO), [COMPANY_CODE] )) 

Totals setting is Auto.

Thank you everyone for your guides & helps.

Regards,

Sarah Azizan