Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
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
stephencredmond
Luminary Alumni
Luminary Alumni

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

View solution in original post

10 Replies
stephencredmond
Luminary Alumni
Luminary Alumni

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
Author

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.

stephencredmond
Luminary Alumni
Luminary Alumni

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

swuehl
MVP
MVP

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
Author

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

Best Regards,

Marian T.

Not applicable
Author

Stephen,

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

Best Regards,

Marian T.

swuehl
MVP
MVP

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).

stephencredmond
Luminary Alumni
Luminary Alumni

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
Author

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