topic How to sum-up only the maximum values based on particular Field? in QlikView

How to sum-up only the maximum values based on particular Field?

Mon, 09 Mar 2015 06:11:59 GMT

Hi QV Team,

Can you help me to total only the maximum values?

Please see the attached excel sheet for more details.

Re: How to sum-up only the maximum values based on particular Field?

mukesh24 — Mon, 09 Mar 2015 06:16:30 GMT

Hi Mrunali,

U can achieve these in backend script as :-

Load max(Net_Value) as MaxNetValue,

SalesOrder,

etc

... group by SalesOrder,...

Regards,

Mukesh

Re: How to sum-up only the maximum values based on particular Field?

Mon, 09 Mar 2015 06:25:15 GMT

Hi Mukesh,

It is not working. Can you upload .Qvw file ?

Re: How to sum-up only the maximum values based on particular Field?

senpradip007 — Mon, 09 Mar 2015 06:34:05 GMT

Could you please explain your requirement

Sales Order	Customer ID	Serial Number	Net Value
10200001	20	3100123	0
			23
			344
			33
			667
			111
			3212

On which basis 3212 value would come ?

Re: How to sum-up only the maximum values based on particular Field?

Mon, 09 Mar 2015 06:47:56 GMT

That is each and every Part.No Values(Rates) based on Serial Number.

Re: How to sum-up only the maximum values based on particular Field?

jonathandienst — Mon, 09 Mar 2015 06:48:43 GMT

The maximum values are subtotals. You should not load these, rather let QV calculate the totals. Or load only the subtotals and not the detail if that is what you require. What you should not do is load both as every expression in your data model will be more complex and (if your data set is large) may perform very badly.

This script will exclude the subtotals:

Pre:

LOAD [Sales Order],

[Customer ID],

[Serial Number],

[Net Value],

RowNo() As RowID

FrOM

(ooxml, embedded labels, table is Sheet1, filters(

Replace(1, top, StrCnd(null)),

Replace(2, top, StrCnd(null)),

Replace(3, top, StrCnd(null))

));

Final:

NoConcatenate

LOAD [Sales Order],

[Customer ID],

[Serial Number],

[Net Value],

RowID

Resident Pre

Where [Sales Order] = Previous([Sales Order])

AND [Customer ID] = Previous([Customer ID])

AND ([Serial Number]) = Previous ([Serial Number])

Order By RowID DESC;

DROP Table Pre;

Now you can use display the data in a straight table with [Sales Order], [Customer ID], [Serial Number] as dimensions and Sum([Net Value]) in an expression.

HTH

Jonathan