topic Re: Why No Totals? in QlikView

Why No Totals?

pkelly — Fri, 11 Sep 2015 08:46:18 GMT

Hi all

I have the following formula in my Pivot Table to calculate Excess Stock Value.

=IF((sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)) > 0,

(sum({<pro_FamilyCode = {01, 02}> } inm_OnHandValue) / sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty)) *

(sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)),

0)

This is calculating our excess stock value on a line by line basis.

I have click "Show Partial Sums" but none are being displayed.

Any ideas?

Regards

Paul

Re: Why No Totals?

alexandros17 — Fri, 11 Sep 2015 08:50:56 GMT

Have you seen on the bottom of the table?

Re: Why No Totals?

jonathandienst — Fri, 11 Sep 2015 08:52:54 GMT

I expect that the conditional is returning false when it is being evaluated at the partial sum level.

You can test this by adding the test expression:

sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)

Does this evaluate to >0? If not, your main expression will return 0.

You could try an expression that use Dimensionality() to make the expression depending on the level at which it is being evaluated.

Re: Why No Totals?

sunny_talwar — Fri, 11 Sep 2015 09:01:19 GMT

You will need to use Sum(Aggr(YourCurrentExpression, YourDimensions))

Re: Why No Totals?

pkelly — Fri, 11 Sep 2015 09:02:43 GMT

Yes, have scrolled down to the bottom and no totals are there.

Re: Why No Totals?

pkelly — Fri, 11 Sep 2015 09:04:16 GMT

I am giving the user the ability to hide / display dimensions - how would this work with that i.e. if I include a dimension in the aggregation but they have chosen not to display it?

Re: Why No Totals?

pkelly — Fri, 11 Sep 2015 09:06:13 GMT

I am getting both positive and negative results - only want to see the positive results.

Re: Why No Totals?

jonathandienst — Fri, 11 Sep 2015 09:10:12 GMT

Paul Kelly wrote:

I am getting both positive and negative results - only want to see the positive results.

In the partial totals as well? Do you get a value when the test expression is positive in the partial total lines?

Re: Why No Totals?

pkelly — Fri, 11 Sep 2015 09:11:46 GMT

Yes I do...

Re: Why No Totals?

jonathandienst — Fri, 11 Sep 2015 09:22:02 GMT

But then I am not sure if I understand your problem. You say you don't want the values when they (test expression values) are negative and you are getting values when they are positive; so what is missing?

Or do you want the partial total to add the details having a positive test expression value and not the negatives? Then you need

Sum(Aggr(

If((Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - Sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)) > 0,

(Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandValue) / Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty)) *

(Sum({<pro_FamilyCode = {01, 02}> } inm_OnHandCostQty) - Sum({<pro_FamilyCode = {01, 02}> } con_CostQuantity)), 0)

, <dim1>, <dim2>, ....

))

Replace <dim1>, <dim2>, ....with a comma separated list of your table dimensions.

Re: Why No Totals?

pkelly — Fri, 11 Sep 2015 09:37:34 GMT

Apologies if I am causing confusion...

When I tried the test that you suggested, my detail lines are showing positive and negative values and I am receiving totals.

The issue I have is that, when I exclude the negative values, the total's disappear.

Will try your suggestion above.