topic Re: Round / Ceil & sumifs in QlikView

Round / Ceil & sumifs

jamesehunt — Wed, 25 Nov 2020 16:16:04 GMT

Hello,

i am looking for a function for the following example:

How can I round / ceil the amount of pallets summed up on the "Ref Number"?

Ref Number	Delivery Typ	Country from	# of Pallets
14700781	Normal	UK	0,2
14700781	Normal	UK	0,2
14700785	Normal	UK	0,9
14700785	Normal	UK	0,1
14700785	Normal	UK	1,02
14700797	Normal	UK	0,01
14700798	Normal	UK	0,1
14700798	Normal	UK	1,02
14700798	Normal	UK	0,5
14700801	Normal	UK	0,45

Expected Result:

14700781: 1 Pallet

14700785: 3 Pallets

14700797: 1 Pallet

14700798: 2 Pallet

14700801: 1 Pallet

Thank you very much for help

Re: Round / Ceil & sumifs

swuehl — Mon, 16 Oct 2017 09:00:08 GMT

Maybe

=Ceil(Sum([# of Pallets]))

as expression in a table chart with dimension [Ref Number]

edit:

Or in the script:

LOAD [Ref Number],

Ceil(Sum([# of Pallets])) as [Ceiled # Pallets]

RESIDENT YourTable

GROUP BY [Ref Number];

Re: Round / Ceil & sumifs

Anil_Babu_Samineni — Mon, 16 Oct 2017 09:01:31 GMT

Try Ceil() / Floor(). I believe you may need this? =Ceil(Sum([# of Pallets]))

Re: Round / Ceil & sumifs

jamesehunt — Tue, 17 Oct 2017 10:13:43 GMT

This function doesn't give me the answer expected.

I would like to see on Country level how many pallets were send out. The expected Result is 83.194.

The function would need to sum mixed pallets on "Ref Document" level excluding the Provider Type "Parcel".

The summed up mixed pallets would need to be round up to the next integer.

I need to see on country level how many pllets have been send out.

Much obliged for your help

Re: Round / Ceil & sumifs

swuehl — Tue, 17 Oct 2017 10:54:20 GMT

Guess your data model is different from what you described in your original post.

For example, what about the 'Ref document' level? is that a hierarchy where 'Ref number' is part of?

Also, 'Provider Type' was not part of the table you described.

If you want any help in an efficient way, please describe your model in all necessary detail, together with the context you want to use the expression and some sample data records and expected result.

If possible, post a small sample application, please.