topic Re: Sum of expression in QlikView

Sum of expression

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a table which few dimensions and few expressions on it. Within the table only one dimension column is visible and others are hidden. With applying Sum of Expressions I get total of expression column which is grouping expression with all fields (example given below).

Branch (Dim), Increase (Exp.), RI (Exp.)

6 , 100%

ABC , 1 , 11.2%

ABC , 0 , -10.1%

ABC , 1 , 5.2%

XYZ , 0 , 23.5%

XYZ , 1 , 10.2%

CDE , 1 , 12.1%

CDE , 1 , 10.2%

CDE , 0 , 13.1%

GHJ , 1 , 11.2%

GHJ , 0 , 4.5%

GHJ , 1 , 10.6%

The reason why Branch names are repeated is because there are other columns which are hidden. The expression for column Increase is =if (RI <= 2.5/1000,1,0). Please note the example above & below has hypothetical numbers.

I want to show a table with distinct values with expressions as above but still grouping it with other hidden columns, like below.

Branch (Dim), Increase (Exp.), RI (Exp.)

6 , 100%

ABC , 2 , 14.2%

XYZ , 1 , 13.5%

CDE , 2 , 12.1%

GHJ , 2 , 11.2%

The table still needs to split the expression figures by other columns but it should show one line per Branch.

Is this possible? Any help will be very much appreciated.

Thanks in advance!!

Re: Sum of expression

sergio0592 — Thu, 28 Dec 2017 16:36:00 GMT

Hi,

Try with : aggr(Sum(Increase),Branch)

Re: Sum of expression

mdmukramali — Thu, 28 Dec 2017 16:38:46 GMT

Hi,

Can you attach sample file with hidden columns.

I think we can use Aggregation function to Achieve it.

Re: Sum of expression

mdmukramali — Thu, 28 Dec 2017 17:10:54 GMT

Hi,

You can Use something Like this:

=Aggr(Sum(Expression),Branch,other dimensions( Hidden Dimensions)

Thanks,

Mukram

Re: Sum of expression

sunny_talwar — Thu, 28 Dec 2017 17:11:34 GMT

Why add dimension and hide them if they are not serving any purpose?

Re: Sum of expression

Anonymous — Fri, 29 Dec 2017 08:48:07 GMT

The hidden dimensions are needed to calculate the Increase expressions. The figure 10 shown at the top of Increase column is "Sum of Rows" and not "Expression Total"

Re: Sum of expression

Anonymous — Fri, 29 Dec 2017 08:51:36 GMT

Hi sample file is attached now. This is showing the problem I am facing and the expected outcome.

Re: Sum of expression

sunny_talwar — Fri, 29 Dec 2017 09:33:52 GMT

Try this expression for Increase

=Sum(Aggr(if(sum([Rate Increase]) >=2.5/1000,1,0), Branch, POL_NO, RISK_CLASS, PREMIUM_CLASS, LOCATION, RISK_NO))

Re: Sum of expression

Anonymous — Fri, 29 Dec 2017 10:46:44 GMT

Hi Sunny,

thanks a lot for this. this is working in the sample file you returned to me however the same is not working in actual document. Don't know why.

Re: Sum of expression

Anonymous — Fri, 29 Dec 2017 10:51:47 GMT

Hi Sunny,

The problem was I had put extra Sum due to which it wasn't working, but when I removed the extra Sum it gave me perfect result.

Thanks a ton!!!