topic Re: How to obtain a partial total by dimension in QlikView

How to obtain a partial total by dimension

Thu, 30 Aug 2012 13:43:37 GMT

Hi all!

Please find image attached:

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?

Thanks in advance.

Marco Pikiri

Re: How to obtain a partial total by dimension

giakoum — Thu, 30 Aug 2012 13:49:25 GMT

maybe because * MZPRT is not part of any aggregation function

Re: How to obtain a partial total by dimension

Thu, 30 Aug 2012 13:57:41 GMT

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

giakoum — Thu, 30 Aug 2012 13:59:14 GMT

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

Thu, 30 Aug 2012 14:10:30 GMT

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

giakoum — Thu, 30 Aug 2012 14:14:06 GMT

maybe a sample file would help solve this faster

Re: How to obtain a partial total by dimension

Thu, 30 Aug 2012 14:23:43 GMT

Sure ok, let's try.

Thanks.

Re: How to obtain a partial total by dimension

giakoum — Thu, 30 Aug 2012 14:27:51 GMT

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

Thu, 30 Aug 2012 14:36:00 GMT

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

giakoum — Thu, 30 Aug 2012 14:42:49 GMT

what expression is Mc? The previous column?

Re: How to obtain a partial total by dimension

Thu, 30 Aug 2012 14:47:11 GMT

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

giakoum — Thu, 30 Aug 2012 15:26:28 GMT

somehting like the attached?

Re: How to obtain a partial total by dimension

Thu, 30 Aug 2012 15:35:41 GMT

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

giakoum — Mon, 10 Sep 2012 07:24:43 GMT

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

http://community.qlik.com/thread/61292

Re: How to obtain a partial total by dimension

Mon, 10 Sep 2012 10:00:47 GMT

Thank you very much! This is the expected result!!!

Re: How to obtain a partial total by dimension

giakoum — Mon, 10 Sep 2012 11:10:01 GMT

Dont thank me! Whiteline is to thank!