Round Up (Ceil) on sub total of a Pivot Table

[Dayna]

Hello All,

I'm hoping you can help me, I'm trying to apply a rounding on the sub total of a pivot table (which is on the field 'ih_nbr') by using 'ceil', but the aggr is applying to one of the rows (leaving the others blank) rather than applying it on the total.

The calculation I'm trying at the moment looks like this:

ceil(aggr(sum(if(ad_zone=1,(idh_qty_inv/idh_conv), (idh_qty_inv/idh_conv)/2)),ih_nbr))

But the outcome is like the following (the pallet spaces column is without the aggr, but the value the sales order should display at).

Sales Order	Invoice Number	Pallet Spaces	ceil(aggr(sum(if(ad_zone=1,(idh_qty_inv/idh_conv), (idh_qty_inv/idh_conv)/2)),ih_nbr))
SO231865	IN191039	16
SO231865	IN191041	26	68
SO231865	IN191107	26
SO231865	Total	68	68
SO232032	IN191138	18	44
SO232032	IN191139	8
SO232032	IN191308	17.36666667
SO232032	Total	43.36666667	44

Any help would be greatly appreciated!!

Kind Regards,

Dayna

[anbu1984]

Are you getting any numbers without Ceil()?

[Gysbert_Wassenaar]

I don't understand what you're trying to do. Why do you think you need to use the aggr function? What are you trying to calculate? Can you post a qlikview document that explains what you want to do?

[Dayna]

Anbu, I'm getting the same amount of numbers if I don't use ceil, so it's something to do with the aggr statement.

Gysbert, I'm trying to apply a rounding on the calculation for the sub total of ih_nbr - I believe I need the aggr, so it can aggregate the calculation for the ih_nbr field. I would like to show the calculation for each line, but for the total, I want to round it up.

[Gysbert_Wassenaar]

The sum function does the aggregation. The aggr function doesn't. That's only necessary if you need to do an aggregation on an aggregration like calculating the maximum of sum values.

[Dayna]

Gysbert,

How would you advise that I meet my objective?

Many thanks,

Dayna

[sunny_talwar]

I think it would be much easier to address your problem if you can post a sample application.

[Gysbert_Wassenaar]

What is your objective? You still haven't explained that yet.

[Dayna]

Gysbert,

My objective is, that I'd like to perform my sub calculation (the sum) on each line of the table, but I'd like the sub total to be rounded up on the pivot to be rounded. If I can't do a rounding on the sub total, could you advise how to generate a sub-total without using QlikView's default functionality?

Many thanks,

Dayna

[sunny_talwar]

Have you explored Dimensionality() before? You can use a different expression, formatting using Dimensionality for your sub-total row.

Chart Dimensionality

HTH

Best,

Sunny