TOTAL in straight-table?

[andradinu]

Hi All,

I've created a straight-table and the total for 'OFF_INVOICE ' should be 12,88%

I use this expression:

=1-column(15)/column(13)

Why return me wrong result in Total?

ps:In attached xls is ok,but in qlikview (printscreen) is wrong

Regards,

Andra

[Anonymous]

Hi,

can u send the application please?

Regards

Erika

[tresesco]

Please reduce the size. Select some field value for what you would want the data o be there in the sample app, then :File-> Reduce Data-> Keep Possible Values.

[andradinu]

with Reduce Data give me 96 MB,also no big difference. Maybe it's better to reload with debugger a few lines ?

[shadoworth]

Hi,

cheking your dashboard I found the problem, even if I didn't found the solution yet.

The point is that when performing the calculation in the Total Row

1- ( [Monthly Trend ] /  [Trend after on invoice ])

Qlik is using the Total of   [Monthly Trend ]  and of  [Trend after on invoice ] calculated in Total mode, NOT as a sum of row.

While [Trend after on invoice ]  has the same value, the values of  [Monthly Trend ]  are different:

6646269 in TOTAL MODE, 11135509 in SUM of ROW.

The 48% is calculated using the first.

At this moment I do not know how to force Qlik to use the total by sum of row, but if someone can explain it to you, you can solve the problem

By my side I will make some test and let you know if I can solve it

[andradinu]

Hi,Andrea

You're right. I have not found the solution, I tried in all ways without success

Thanks for post

[shadoworth]

I'm not sure that there is a solution. Basically you are calculating the Total row always as a sum of rows and for some reasons in your calculation this results is different from the Expression Total result. Thus you cannot change one column to something that is not a sum of rows and that at the same time uses other expressions.

Maybe (but only you can know it) you can change your data model in a way such that the Expression Total and the Sum of Rows became the same, so that it will work correctly.

[IAMDV]

Hi Andra,

It works okay if you have them as field names (See the attached image). However, you have them as calculated fields and as I understand the calculation is very big expression and you're referring the label names or column numbers to calculate the percentage difference. You can't calculate totals based on expression labels or column numbers because they are not persistent objects (fields) in QlikView. This reason being there are multiple rows and QlikView needs to know the type of aggregation. We as users know that we wanted to divide two columns and subtract from 100 to get the percentage difference. However, QlikView can't interpret the type of aggregation so it returns a NULL value. And if you change the Total Mode to "Sum of Rows" then you would get 60.88% which is sum of all the rows (Incorrect answer).

To solve this issue there are two solutions:

1. I'd suggest moving the calculation to script i.e. [Monthly Trend] &[Trend after on invoice] and you can calculate percentage difference as show above

2. If you can't move the calculation to script then I'd suggest moving the calculation of both the expressions into variable and then we can use the variable with an aggregation function. See below example:

vMonthlyTrend = (((sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  SUMA_FACTURA))+sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  NEP_SUMA_FACTURA))+sum(if((CMTD='1'),SUM_NETIPARITE)))

-

((sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  SUMA_FACTURA))+sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  NEP_SUMA_FACTURA))+sum(if((CMTD='1'),SUM_NETIPARITE)))

*SUM(IF(luna_off=month(today()),OFF_INVOICE))))/(sum(if(CMTDzl='1',zl2013))/sum(if(CMzl='1',zl2013))))

vInvoiceTrend = ((sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  SUMA_FACTURA))+sum(if((left(grupap, 1)='A' or left(grupap, 1)='B' or left(grupap, 1)='C') and (CMTD='1'),  NEP_SUMA_FACTURA))+sum(if((CMTD='1'),SUM_NETIPARITE)))/(sum(if(CMTDzl='1',zl2013))/sum(if(CMzl='1',zl2013))))

And finally you can use:

1- (SUM($(vMonthlyTrend)) / SUM($(vInvoiceTrend)))

Note: Please don't include "=" (equal to) sign while assigning the variable in the Variable Overview window.

I hope this helps! Please let me know if you have any questions.

Cheers,

DV

[andradinu]

I tried with variable but appears with red . How can I include in the script?

Thank you very much for everything you did

[IAMDV]

You're welcome. Please can you post the sample app? It's easier to work with the sample app. Please can you share the sample app?

Cheers,

DV

[andradinu]

Is posted above (see the response to Erika Jain). Indeed, it's pretty big (106 MB), but it did not work to make it smaller.

Many thanks again!