topic Straight table column total problem in QlikView

Straight table column total problem

Mon, 23 Jan 2012 14:09:40 GMT

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.

Straight table column total problem

stephencredmond — Mon, 23 Jan 2012 14:46:18 GMT

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

Straight table column total problem

Mon, 23 Jan 2012 15:01:22 GMT

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.

Straight table column total problem

stephencredmond — Mon, 23 Jan 2012 15:09:26 GMT

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

Straight table column total problem

swuehl — Mon, 23 Jan 2012 15:14:11 GMT

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

Straight table column total problem

Mon, 23 Jan 2012 15:47:11 GMT

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

Best Regards,

Marian T.

Straight table column total problem

Mon, 23 Jan 2012 16:02:05 GMT

Stephen,

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

Best Regards,

Marian T.

Straight table column total problem

swuehl — Mon, 23 Jan 2012 16:33:50 GMT

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

Straight table column total problem

stephencredmond — Mon, 23 Jan 2012 16:39:11 GMT

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

Straight table column total problem

Mon, 23 Jan 2012 17:50:23 GMT

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

Straight table column total problem

Mon, 23 Jan 2012 17:52:58 GMT

Swuehl, thanks too & regards !!