Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Straight table column total problem

Hi All.

I have a straight table with 7 columns and the last column (KPI) give wrong total. This must be: column(6)/column(5)*30. Screen shot:

kpi5.jpg

See the QVW and XLS atached.

(.qvw have limited load (100 lines) because is too large but full .xls atached)

Any suggestions?

Thanks in advance, Marian T.

1 Solution

Accepted Solutions
stephen-a_redmo
Valued Contributor II

Straight table column total problem

The expression for "Valoare Stoc" is set to Sum of Rows.  But this is a sum of a ratio multiplication so it is not valid to have it in the Total calculation (6/5*30) because the actual total is different than the sum of the rows.

If the actual KPI should be based off the sum of rows, you need to use an AGGR to calculate Valoare Stoc:

Sum(Aggr((sum(if(left(grupap, 1)='B',STOCY))-sum(if(left(grupap, 1)='B',total_qty))) * ((SUM({$ < grupap={'B*'}, [luna_f] = {"< $(=luna_f +1)"}>} SUMA_FACTURA-SUMA_FACTURA*DISCOUNT_FACTURA/100)) / (SUM({$ < grupap={'B*'}, [luna_f] = {"< $(=luna_f +1)"}>}   CANTITATE*FACTOR_CONVERSIE))), COD_PRODUS))

Regards,

Stephen

10 Replies
stephen-a_redmo
Valued Contributor II

Straight table column total problem

Hi,

I can't really see your problem in the provided QVW (zero values) and can't reload from just the xls.

Are you sure that you have "Expression Total" as the total mode for this expression?  Not something like "Avg of Rows"?

Regards,


Stephen

Not applicable

Straight table column total problem

Hi Stephen,

I put QVW only for expression example. KPISalesTrade.qvw is original qvw and have limited load (100 lines) with debug because is too large (136 MB).

Regards,

Marian T.

stephen-a_redmo
Valued Contributor II

Straight table column total problem

Hi Marian,

I understand that the file might be too large but that means that I see col6/col5*30 as 0/0*30 and see the correct answer of 0!  So I can't see where your problem is.

You do have an error on the Sales YTD expression because the fields are not found - but that might be your data load is limited.

It should work though.

What if you write the expression out "long hand" without the column() functions?  Does it calculate correctly?


Regards,

Stephen

MVP
MVP

Straight table column total problem

Marian,

it's pretty hard to see anything, since your sample file shows zeros in 6 of 7 columns.

Maybe you could try to do a normal load, but then restrict your data using selections and then use File->Reduce Data-> Keep possible values (and save to different file name). restrict as much as possible but the issue should still be visible to us.

I only noticed that you are using some field names in your expressions in the chart, that are actually not anymore part of your data model. Could be due to the limited load.

Another thing:

You are using set expression like {$ < grupap={'B*'} >}

if this should be a search expression with wildkey *, I believe you should use double quotes instead of single quotes.

Both does not explain why your total is wrong, so please try to upload a sample where we can see the issue.

Regards,

Stefan

Not applicable

Straight table column total problem

Ok, I put full model on my server: http://www.freestore.ro/qlikview/KPI.rar 

Best Regards,

Marian T.

Not applicable

Straight table column total problem

Stephen,

No, not calculated correctly with "long hand" expression. I tried did not work unfortunately.

Best Regards,

Marian T.

Highlighted
MVP
MVP

Straight table column total problem

The total mode for column 6 (valoare stoc) is set to sum of rows, not expression total, this evaluates to a different total, thus the difference for your column 7 (which uses expression total).

stephen-a_redmo
Valued Contributor II

Straight table column total problem

The expression for "Valoare Stoc" is set to Sum of Rows.  But this is a sum of a ratio multiplication so it is not valid to have it in the Total calculation (6/5*30) because the actual total is different than the sum of the rows.

If the actual KPI should be based off the sum of rows, you need to use an AGGR to calculate Valoare Stoc:

Sum(Aggr((sum(if(left(grupap, 1)='B',STOCY))-sum(if(left(grupap, 1)='B',total_qty))) * ((SUM({$ < grupap={'B*'}, [luna_f] = {"< $(=luna_f +1)"}>} SUMA_FACTURA-SUMA_FACTURA*DISCOUNT_FACTURA/100)) / (SUM({$ < grupap={'B*'}, [luna_f] = {"< $(=luna_f +1)"}>}   CANTITATE*FACTOR_CONVERSIE))), COD_PRODUS))

Regards,

Stephen

Not applicable

Straight table column total problem

Stephen, Thank you very much, it worked like a charm!