Customize Partial Sum...

[israrkhan]

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

[JonnyPoole]

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]

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]

could you share your qvw please

[israrkhan]

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]

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]

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]

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 1	Header 2	Header 3	Header 4	Header 5
ControlCode	AccountCode	Month	Amount	Sum(this column is not in table)
10	1	Jan	100
10	1	Jan	-200	-100. dont want to show this value
10	2	Jan	150
10	2	Jan	-50	100
11	3	Feb	500
11	3	Feb	-200	300
11	4	--	-700
11	4	--	500	-200(convert this value to positive value)

[MK_QSL]

have you gone through below?

How to Create a Profit and Loss Statement in QlikView

[israrkhan]

Thanks Manish,

i have read this..

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

anyway

Many Thanks...