Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Specialist II
Specialist II

Customize Partial Sum...

i guys,

i want to customize partial sum, in pivot table, is this possibe...

I have a pivot table with three dimension and one expression,

i have negative values in pivot table, and i dont want to show negative values, so i am showing zero instead of negative values..

but my sum is still same, i want to get the sum of only visible value,

is there a way to do that?

Many Thanks

Khan

10 Replies
Highlighted
Employee
Employee

Re: Customize Partial Sum...

Something like...

sum (    {<CombinedID={"=sum(aggr(sum(Sales),dim1,dim2,dim3)) >=0"}>}  Sales)

Where CombinedID is a unique identifier of Dim1,Dim2,Dim3... setup as a calculated field in the load via autonumber().

I may have overcomplicated it but let me know what you think. I haven't tested it yet.

Highlighted
MVP
MVP

Re: Customize Partial Sum...

Don't know whether this will help you or not.. but you can do aggr in the script itself and make 0 for all negative aggregated values..

Highlighted
Creator III
Creator III

Re: Customize Partial Sum...

could you share your qvw please

Highlighted
Specialist II
Specialist II

Re: Customize Partial Sum...

Hi Jonathan,

Thanks for the reply..

let me explain a bit more..

i have 3 master tables, Company, Control, Account, and one transaction table.

my Dimension are CompanyCode, ControlCode, AccountCode,

there are many controlCode under one company, and there are many AccountCode under one ControlCode.

now let say for ControlCode 10, there are 20 Accountcode, some of them having negatives values,(my transaction are on AccountCode Level)

now under controlCode 10 i dont want to sum any negative value,

and under controlcode 11 i want to convert negative values to positive values...

how to write this complex expression...

i can do this all in expressions, but then my partial sum goes wrong, means remains same...even if i converto negatives to positive , or id i converet negative value to 0...

i think its clear now...

Thanks Again.....

Highlighted
Specialist II
Specialist II

Re: Customize Partial Sum...

Guys, Please read my detail answer to Jonathan,

and kindly help me, i spent 5, 6 hour...but stuck...no luck

actually i am building a Balance sheet...might be one of you have worked on already, and could share the experience...

Many Thanks

Highlighted
MVP
MVP

Re: Customize Partial Sum...

T1:

Load Company, Control, Account, Value From TableName;

T2;

Load Company, Control, Account, If(Sum(Value) < 0 , 0, Sum(Value)) as TotalValue Resident T1

Group By Company, Control, Account;

Now use, SUM(TotalValue) instead of Value.... Not sure this would help or not..

Highlighted
Specialist II
Specialist II

Re: Customize Partial Sum...

Dear Manish,

Thank you for your time...

i think you can help me, but let me try to explain a bit more...  🙂

i have data like below table...

if you see in the table, under control code 10 if sum(Amount) for any AccountCode is negative then dont want to show this value in table...for each AccountCode there are 2 values, and this condition is only for Control code 10...

but for the ControlCode 11 if there is negative sum, then i have to convert it in to positive value and have to show in report...

is it clear now???

Header 1Header 2Header 3Header 4Header 5
ControlCodeAccountCodeMonthAmountSum(this column is not in table)
101Jan100
101Jan-200-100. dont want to show this value
102Jan150
102Jan-50100
113Feb500
113Feb-200300
114---700
114--500-200(convert this value to positive value)
MVP
MVP

Re: Customize Partial Sum...

Highlighted
Specialist II
Specialist II

Re: Customize Partial Sum...

Thanks Manish,

i have read this..

but my data structure is totally different than this...

anyway

Many Thanks...