15 Replies Latest reply: Sep 10, 2012 7:10 AM by ioannis giakoumakis

# How to obtain a partial total by dimension

Hi all!

Mc = Count(total <DESTINAZIONE, MEZZO> MVCDE) * MZPRT // MVCDE and MZPRT are two db fields

Tot Mc = sum(total <DESTINAZIONE> Mc)

Why am I getting 0 values as result? I don't understand where I'm making mistake, the red values shown in figure are the result I would expect...

Do you need to know more details about db structure?

--

Marco Pikiri

• ###### Re: How to obtain a partial total by dimension

maybe because * MZPRT is not part of any aggregation function

• ###### Re: How to obtain a partial total by dimension

Each "MEZZO" ("DUMPER 1", "DUMPER 2", etc.) has its own MZPRT: which should be the right aggregation function to use in this case? Seeing from documentation "Only()" function, I suppose: is that right?

• ###### Re: How to obtain a partial total by dimension

only is automatically used by QlikView when there is no other stated. try min or max, both should work as it is only one value for each mezzo

• ###### Re: How to obtain a partial total by dimension

Changing "Mc" expression to:

Count(total <DESTINAZIONE, MEZZO> MVCDE) * min(total <MEZZO> MZPRT)

or to:

Count(total <DESTINAZIONE, MEZZO> MVCDE) * max(total <MEZZO> MZPRT)

still outputs 0 values again

But as of my newbieing to QV now another question arises: is it correct the "total <MEZZO> " notation I've put above in the min-max functions?

• ###### Re: How to obtain a partial total by dimension

maybe a sample file would help solve this faster

• ###### Re: How to obtain a partial total by dimension

Sure ok, let's try.

Thanks.

• ###### Re: How to obtain a partial total by dimension

There is no such field as Mc :

So in Tot Mc expression : =sum(total <DESTINAZIONE> MVCDE)

should work

• ###### Re: How to obtain a partial total by dimension

In fact Mc is an expression, not a field.

Values output by:

=sum(total <DESTINAZIONE> MVCDE)

are not correct, i.e. for the ALTRO "DESTINAZIONE" we will have:

27 + 17.5 = 44.5

and not 124 as I can see. There should be a sum of "Mc" expressions, not of MVCDE field.

Please see that MVCDE in my db is an auto-incremental ID field that I use just to count the occurrences of what I'm evaluating.

• ###### Re: How to obtain a partial total by dimension

what expression is Mc? The previous column?

• ###### Re: How to obtain a partial total by dimension

Yes, i.e. the column after "DESTINAZIONE" dimension.

After "Mc" there is "Tot Mc" expression that is the sum of "Mc" values grouped by "DESTINAZIONE" dimension.

• ###### Re: How to obtain a partial total by dimension

somehting like the attached?

• ###### Re: How to obtain a partial total by dimension

In the attached, "Tot Mc" is not correct, but "Tot Mc new" almost.

I say almost because I would expect the same values also for "DUMPER 2" MEZZO, and not a series of 0..

Anyhow... ... what a concatenation of functions! But where could I start to understand the logic behind this solution? i.e., which could be the best approach?

• ###### Re: How to obtain a partial total by dimension

Sum(total<DESTINAZIONE> aggr(distinct Count(MVCDE)*MZPRT, DESTINAZIONE, MEZZO))