topic Re: Sum if in Pivot table in QlikView

Sum if in Pivot table

Anonymous — Sat, 16 Nov 2024 05:08:15 GMT

Hi All,

Please let me know how to use column() or column name in sum if statement

Example

Sum(if(Indicator='N', Column(16))

Sum(if(Indicator='N', [Column Name])

I am not able to use both statement in sum if statement

Thanks in advance

Re: Sum if in Pivot table

r3iuk — Mon, 26 Nov 2018 15:05:30 GMT

I think all you might need an extra ')' at the end of the statement?

Sum(if(Indicator='N', Column(16)))

Sum(if(Indicator='N', [Column Name]))

It might be useful to see your expression in the column being referenced and what kind of outcome you are trying to achieve.

Re: Sum if in Pivot table

vmoreno2605 — Mon, 26 Nov 2018 14:55:01 GMT

It would be useful to see your pivot table

Re: Sum if in Pivot table

sunny_talwar — Mon, 26 Nov 2018 15:05:54 GMT

Is Column(16) an expression or just a field name? If it is an expression involving Sum, Avg, Min, Max or any kind of aggregation, then this might not really work for you because you will be doing Aggregation over an aggregation. May be a sample might help us understand what you are trying to do to help you better here.

Re: Sum if in Pivot table

marcus_sommer — Mon, 26 Nov 2018 15:13:56 GMT

You could use something like:

if(Indicator='N', Column(16))

but not within aggregation-functions like sum(). If you really want to apply an aggregation you need to use your origin expression - probably wrapped with an aggr() function like:

sum(aggr(YourOriginExp, Dim1, Dim2))

- Marcus

Re: Sum if in Pivot table

Anonymous — Mon, 26 Nov 2018 17:07:53 GMT

Thanks for the reply,

can you send me the syntax or sample code with ')'

Re: Sum if in Pivot table

Anonymous — Mon, 26 Nov 2018 17:18:47 GMT

Hi,

Thanks for the reply.

Yes the column(16) has the below expression with OR condition

sum({<[Indicator={'Y'}>}HC * [Better Estimate_Input]
+
(sum({<[Indicator={'N'}>}HC_Input)* AVG({<[Event Id]={'BAM','BAT'}>} [PY TC US]) )
)

next column i am using the sum if statement

sum(if(indicator='N', column(16)).

Reason using Sum if is, user inputing mulitple values in the front end / table box

Re: Sum if in Pivot table

sunny_talwar — Mon, 26 Nov 2018 17:48:49 GMT

Do you have Indicator as one of your dimensions in the chart?