topic Re: Sum total for a complex calculation in QlikView

Sum total for a complex calculation

Fri, 13 Dec 2013 10:41:18 GMT

Hi all.

I have this complex calculation in a pivot/straight chart and I cannot find the sum TOTAL for the whole column with all the trials I have made within my knowledge...

The following is my calculation.

=if(len([SalesInvHeader.Sell-to Customer Name])>0 and [SalesInvHeader.Payment Terms Code]<>'PP' and SalesInvLine.Type=2 or [SalesInvLine.Prepayment Line]=1,

[CustLedgerEntry.Sales (LCY)] -

(Sum

( if(CustLedgerEntry.DueDays>[SalesInvHeader.Payment Terms Code],

([CustLedgerEntry.Sales (LCY)] * ([SalesInvHeader.Payment Terms Code]*(0.25/365))) + ([CustLedgerEntry.Sales (LCY)] * (CustLedgerEntry.OverDueDays*(0.5/365))),

[CustLedgerEntry.Sales (LCY)] * (CustLedgerEntry.DueDays*(0.25/365))))

+ sum(SalesInvLine.COGS)))

The only thing I need to do is ideally enclose the whole expression in a sum(TOTAL expr) but it doesnot work.I know it looks complicated but the logic is simple.It calculates the profitability of a customer.I calculates sum for the line level but not as a whole.

Please please help me out.Thanks a million in advance.

Naima

Re: Sum total for a complex calculation

swuehl — Fri, 13 Dec 2013 10:43:21 GMT

Try the total mode 'sum of rows' in a straight table, and in a pivot table, look into 'sum of rows using advanced aggregation' (a chapter in the HELP file).

Re: Sum total for a complex calculation

Fri, 13 Dec 2013 10:47:55 GMT

Thanks a lot for the quick reply swuehl

.I need to use the total value for calculation in the next column of the table, so total mode wouldnot help me.I will check out the advanced aggregation in help.I always thought aggregation is performed based on grouping of a particular dimension.Here,I dont require any grouping too.Thanks again.