topic Need sum of if column in App Development

Need sum of if column

anil2185 — Tue, 27 Mar 2018 06:34:46 GMT

Hi All,

I am struck in a situation where I need to show sum of a column in straight table of Qliksense.

Below is the expression.

if(Above(Total if(column(1)>column(6),

if(MonthName(MonthEndDate) = Monthname($(vselectedDate)) , Day(DATE_INVOICE_DATE)

, if(Num(MonthEndDate) < $(vselectedDate) , Day(MonthEndDate))),

(column(1)-column(6))/column(2)

))<0,0,

if(column(1)>column(6),

if(MonthName(MonthEndDate) = Monthname($(vselectedDate)) , Day(DATE_INVOICE_DATE)

, if(Num(MonthEndDate) < $(vselectedDate) , Day(MonthEndDate))),

Fabs((column(1)-column(6))/column(2))

))

Output of this statement is:-

------------------

3.3

I want the sum of these lines at top.

Thanks in advance.

Regards,

Anil

Re: Need sum of if column

petter — Tue, 27 Mar 2018 08:41:05 GMT

Aggr() function could be of help to you in this situation - I think it would have to look like this:

Sum(Aggr(

Sum( .....your-expression-from-above... ) ,

Dim1, Dim2, .....

))

Re: Need sum of if column

sunny_talwar — Tue, 27 Mar 2018 11:30:54 GMT

Also check here...

http://help.qlik.com/en-US/qlikview/November2017/Subsystems/Client/Content/ChartFunctions/NestedAggregations/sum-of-rows…

but once you use this, you will not be able to use column() function... you will either have to use the actual expression or you can use variables...

Re: Need sum of if column

petter — Tue, 27 Mar 2018 12:14:31 GMT

Sunny's response reminds me that you will have to replace all the Column(...) references with the actual expressions behind each of them into you expression before you wrap it into an Aggr() like I suggested. That might mean that you will get quite a large expression....