Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
israrkhan
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
JonnyPoole
Employee
Employee

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.

MK_QSL
MVP
MVP

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..

Anonymous
Not applicable

could you share your qvw please

israrkhan
Specialist II
Specialist II
Author

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.....

israrkhan
Specialist II
Specialist II
Author

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

MK_QSL
MVP
MVP

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..

israrkhan
Specialist II
Specialist II
Author

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)
MK_QSL
MVP
MVP

israrkhan
Specialist II
Specialist II
Author

Thanks Manish,

i have read this..

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

anyway

Many Thanks...