# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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:

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.

Tags (3)
1 Solution

Accepted Solutions
Highlighted
Luminary

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

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

Luminary

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

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

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

Highlighted
Luminary

## 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!