topic Re: TOTAL in straight-table ? in QlikView

TOTAL in straight-table?

andradinu — Thu, 19 Dec 2013 10:23:58 GMT

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

Re: TOTAL in straight-table ?

Gysbert_Wassenaar — Thu, 19 Dec 2013 10:25:24 GMT

Try setting the total mode to sum of rows.

Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 10:32:38 GMT

48% too with total mode to sum of rows

Thanks a lot anyway!

Re: TOTAL in straight-table ?

Anonymous — Thu, 19 Dec 2013 10:33:19 GMT

Use Expression Total, instead of Sum of rows.

Expression Total will give you correct results.

Regards

Nitin

Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 10:42:47 GMT

Nitin, with Expression Total also wrong result (48%)

Thank you too

Re: TOTAL in straight-table ?

tresB — Thu, 19 Dec 2013 10:46:11 GMT

Try putting sum() around your expression:

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

Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 10:56:06 GMT

Tresesco, whit sum give me "Allocated memory exceeded" . Many thanks too

Re: Re: TOTAL in straight-table ?

IAMDV — Thu, 19 Dec 2013 11:12:07 GMT

Did you try the below with "Expression Total" as TOTAL Setting?

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

Assuming these are the correct Label Names!

Cheers,

Re: Re: Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 12:05:13 GMT

48%

Thanks you too

Re: TOTAL in straight-table ?

tresB — Thu, 19 Dec 2013 12:06:51 GMT

Could you please post your example app with sample data?

Re: TOTAL in straight-table ?

Anonymous — Thu, 19 Dec 2013 12:11:02 GMT

Hi,

can u send the application please?

Regards

Erika

Re: TOTAL in straight-table ?

tresB — Thu, 19 Dec 2013 12:46:52 GMT

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.

Re: Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 13:31:09 GMT

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

Re: TOTAL in straight-table ?

shadoworth — Thu, 19 Dec 2013 14:44:27 GMT

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

Re: Re: TOTAL in straight-table ?

andradinu — Thu, 19 Dec 2013 15:21:01 GMT

Hi,Andrea

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

Thanks for post

Re: Re: TOTAL in straight-table ?

shadoworth — Thu, 19 Dec 2013 16:23:53 GMT

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.

Re: Re: Re: TOTAL in straight-table ?

IAMDV — Fri, 20 Dec 2013 08:41:38 GMT

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,

Re: TOTAL in straight-table?

andradinu — Fri, 20 Dec 2013 09:27:04 GMT

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

Thank you very much for everything you did

Re: Re: TOTAL in straight-table?

IAMDV — Fri, 20 Dec 2013 10:26:18 GMT

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,

Re: TOTAL in straight-table?

andradinu — Fri, 20 Dec 2013 10:32:24 GMT

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!