topic Re: Customize Partial Sum... in QlikView

Customize Partial Sum...

israrkhan — Tue, 17 Mar 2015 19:10:49 GMT

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

Re: Customize Partial Sum...

JonnyPoole — Tue, 17 Mar 2015 19:24:38 GMT

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.

Re: Customize Partial Sum...

MK_QSL — Tue, 17 Mar 2015 19:29:56 GMT

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

Re: Customize Partial Sum...

Anonymous — Tue, 17 Mar 2015 19:34:07 GMT

could you share your qvw please

Re: Customize Partial Sum...

israrkhan — Tue, 17 Mar 2015 19:47:42 GMT

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

Re: Customize Partial Sum...

israrkhan — Tue, 17 Mar 2015 19:50:39 GMT

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

Re: Customize Partial Sum...

MK_QSL — Tue, 17 Mar 2015 19:57:59 GMT

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

Re: Customize Partial Sum...

israrkhan — Tue, 17 Mar 2015 20:15:22 GMT

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)

Re: Customize Partial Sum...

MK_QSL — Tue, 17 Mar 2015 21:03:48 GMT

have you gone through below?

How to Create a Profit and Loss Statement in QlikView

Re: Customize Partial Sum...

israrkhan — Tue, 17 Mar 2015 21:34:28 GMT

Thanks Manish,

i have read this..

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

anyway

Many Thanks...

Re: Customize Partial Sum...

JonnyPoole — Wed, 18 Mar 2015 14:56:24 GMT

Israr , do you have a mapping table that defines which account code converts negative to positive and which one disregards negatives ?

You could add a field called 'converter' to that mapping table that is either 0 (disregard negatives) , -1 (convert negatives) , and 1 (leave as is)

Your expression would be

sum ( Value * converter)

-> any records associated to a code that is 0 get a 0 value in details and sums

-> any records associated to a code that is -1 get a positive value in details and sums

-> any records associated to a code of 1 get no change at all

Would this work ?