Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Attend QlikWorld 2020 and hear keynote speaker, Malcolm Gladwell. Register by February 29th to save $200. Learn More
nancybricenoa
New Contributor

Straigh table with Total for Max and % calculation

Hello.

I have a straight table with several columns that total is sum(values) but I have one column = max(algo) and the total for this column I forced to sum all the max(algo).

So far so good...

In the picture attached you can find the table and the column Credit Limit has max(algo) and totasl was forced to sum all max values, the other column is amount Over Credit that is sum(values) and its total also it is a sum so, the % over credit has a formula=Column(8)/Column(5) and for every row % is OK, but in gran total appear 440% and should be -9.585.159/32.814.074 =29%

 

Can anyone help me please!

Thanks

Nancy

1 Solution

Accepted Solutions
Highlighted

Re: Straigh table with Total for Max and % calculation

Try this

(Sum(Aggr(Sum(If(Variable = 'AR', Debt, 0)), Area, [Sub-Area], [Higher Code], [Higher Name]))
/
Sum(Aggr(Max(If(Variable = 'AR', CreditLine, 0)), Area, [Sub-Area], [Higher Code], [Higher Name])))
-
1

View solution in original post

8 Replies
Highlighted
Partner
Partner

Re: Straigh table with Total for Max and % calculation

Try this expression:

if(Dimensionality()=0,

max(algo)/sum(values),

Column(8)/Column(5))

 

Highlighted
nancybricenoa
New Contributor

Re: Straigh table with Total for Max and % calculation

Hello,

 

I did that but it is not correct. the fact here is that % = column(8)/ Column(5) but according to what you can see in the picture of my table, the column(8) =column(4))-(column(5) and colum(5) =max (algo) with total forced to sum(all rows).

My expression here for %over credit is below:

If(Dimensionality() = 0,
(sum(TOTAL(IF(Variable='AR', Valor,0)))-max(TOTAL(IF(Variable='AR',Linea_credit)))) / (max(TOTAL(IF(Variable='AR',Linea_credit)))),
((column(4))-(column(5)))/((column(5))))

thanks!

Nancy

Highlighted

Re: Straigh table with Total for Max and % calculation

What you need here is Sum(Aggr()) function around your individual expressions

(Sum(Aggr(ExpressionBehindColumn4, Area, [Sub-Area], [Higher Code], [Higher Name]))
/
Sum(Aggr(ExpressionBehindColumn5, Area, [Sub-Area], [Higher Code], [Higher Name]))) - 1

 

Highlighted
nancybricenoa
New Contributor

Re: Straigh table with Total for Max and % calculation

Hi, 

thanks!

nut I have an error with Sum(aggr(Max)).

My expression is:

=(Sum(Aggr(IF(Variable='AR',valor,0), Area, [Sub-Area], [Higher Code], [Higher Name])) / Sum(Aggr(max(IF(Variable='AR', valor)), Area, [Sub-Area], [Higher Code], [Higher Name]))) - 1

 

Please help!

Highlighted

Re: Straigh table with Total for Max and % calculation

Can you share the expressions behind Column4 and Column5 here?

Highlighted
nancybricenoa
New Contributor

Re: Straigh table with Total for Max and % calculation

Yes!

 

Column(4) expression is =sum(IF(Variable='AR' ,Debt,0))

Column(5) expression is =max(IF(Variable='AR' ,CreditLine,0))

and I need to calculate %overcredit= (colum(4) -column(5))/column(5)

thanks!

 

PD: Please note that only have problems with %total, because the % for every row is ok. Attaching the table again

Capture.JPG

Highlighted

Re: Straigh table with Total for Max and % calculation

Try this

(Sum(Aggr(Sum(If(Variable = 'AR', Debt, 0)), Area, [Sub-Area], [Higher Code], [Higher Name]))
/
Sum(Aggr(Max(If(Variable = 'AR', CreditLine, 0)), Area, [Sub-Area], [Higher Code], [Higher Name])))
-
1

View solution in original post

Highlighted
nancybricenoa
New Contributor

Re: Straigh table with Total for Max and % calculation

THANKS!!!!!

It works!!!! Finally!!!! I'm so tired!!!

jajajajaja

Thanks a lot!