Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
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
Luminary
Luminary

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
Luminary
Luminary

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

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.

Luminary
Luminary

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

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

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

Best Regards,

Marian T.

Not applicable

Stephen,

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

Best Regards,

Marian T.

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

Luminary
Luminary

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

Not applicable

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