topic Ask: Partial Sum in Pivot Table to Sum Expression with condition in QlikView

Ask: Partial Sum in Pivot Table to Sum Expression with condition

Mon, 12 Jan 2015 07:23:36 GMT

Hi. I'm qlikview 11 user and I have a problem with my Account Receivable report.

As you can see in the picture below, I want to add a partial sum in Pivot table to OVERPLAFON column which only have a positive value.

This is the formula for TOTAL and OVERPLAFON column.

TOTAL = [BLMJDKW] + [BLMJTTEMPO] + [1-14 HARI] + [15-29 HARI] + [>30 HARI] + [UNKNOWN]

OVERPLAFON = [TOTAL] - [PLAFON].

IF I just Sum it all (both positive and negative value in OVERPLAFON column), it'll give a not valid value.

So, that's why I just want to sum a positive value and I'm going to assign 0 to negative value.

I've tried use an condition expression in column OVER, but it doesn't work.

It always gives 0 value in Partial Sum row.

I use this expression in OVER column:

IF ( ((SUM(TOTALRUPIAH)+SUM(RUPIAH))-SUM(NPLAFON)) > 0,

((SUM(TOTALRUPIAH)+SUM(RUPIAH))-SUM(NPLAFON)), SUM(0)

)

NOTE: ((SUM(TOTALRUPIAH)+SUM(RUPIAH)) is equal with TOTAL column and SUM(NPLAFON) is equal with PLAFON column.

That's a query column name in my mssql server.

With that expression, I can change negative value and replace it with 0 value but it also gives 0 value in Total.

Anyone can help me?

Hope you guys understand with my poor english ability because I can't speak english well.. hehehe

Thanks

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Mon, 12 Jan 2015 10:52:36 GMT

Hi Teddy,

Here i have attached the sample qvw.hope this will satisfy your requirement.

Thanks

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Tue, 13 Jan 2015 01:43:17 GMT

Hi Sudha,

Thanks for the help..

if I see from your file, you use straight table.

In my case, I can't get the valid value from partial sum in pivot table. (Pivot table > Properties > Presentation > Show Partial Sums)

For your information, I've tried RangeMax too, but it keep gives me zero value when I use Partial Sums in Pivot table.

Here, I've attached your file again with some additional changes.

Hope you can see that file, and you'll find the point what I meant.

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Tue, 13 Jan 2015 02:33:17 GMT

Hi,

PFA the app.

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

kogasawara — Tue, 13 Jan 2015 02:34:18 GMT

How about this expression in pivot table?

sum(aggr(if(sum(F1-F2)>0,sum(F1-F2)),F1,F2))

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Tue, 13 Jan 2015 03:32:49 GMT

Hi kogasawara and Navdeep Kumar,

Thanks for your help.

@kogasawara : I can't figure out with your expression. I think that expression is not suitable for my case.

@Navdeep Kumar:

as you can see, your expression works because there's no sum inside if condition. But in my case, i need to have sum expression inside if condition. e.g. : sum( if( sum(F1-F2)>0, sum(F1-F2), 0) )

however, this come out with a result in "error in expression".

This is what i'm facing right now :

=sum(if(F1-F2>0,F1-F2,0)) -> result OK, but i need to have sum inside if condition.

=if(sum(F1-F2)>0,sum(F1-F2),0)) --> it change the negative value to positive but it gives zero in total.

=sum( if( sum(F1-F2)>0, sum(F1-F2), 0) ) --> I need this kind of expression, but qlikview read it as an error. This is what I actually need.

anyone can help?

thanks

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Tue, 13 Jan 2015 03:50:49 GMT

Hey,

can u attach your app.

Re: Ask: Partial Sum in Pivot Table to Sum Expression with condition

Tue, 13 Jan 2015 12:20:25 GMT

Hi Teddy,

Try this,

=Sum(Aggr(RangeMax(sum(F1-F2), 0), F1, F2))

Thank you @jonathan dienst