topic Re: count / sum Problem in QlikView

count / sum Problem

Fri, 21 Sep 2012 14:31:46 GMT

sorry ... but i need help

hi had this function - works very well:

count({< MA_BAUREIHE = {'ELION','ELION_HYBRID','EVOS'} >} distinct BI_VERTRIEBSBELEG_NR&BI_VERKAUFSBELEG_POS )

but now i have to add a filed with quantity: BI_MENGE. It means my count value * BI_MENGE.

I tried al lot off different things ... but nothing is working

Aggr(DISTINCT BI_VERTRIEBSBELEG_NR&BI_VERKAUFSBELEG_POS, sum (BI_MENGE ))

.....

please help

Re: count / sum Problem

swuehl — Fri, 21 Sep 2012 14:41:02 GMT

We probably need to know where / in which context you are using this expression (this might include needing to know a bit about your data model).

In a very simple case, you could try something like

=count({< MA_BAUREIHE = {'ELION','ELION_HYBRID','EVOS'} >} distinct BI_VERTRIEBSBELEG_NR&BI_VERKAUFSBELEG_POS )

* sum(BI_MENGE)

Re: count / sum Problem

Mon, 24 Sep 2012 06:14:08 GMT

no ... your formula give me a wrong result ... match to high

what you must now about my data model ?

Re: count / sum Problem

swuehl — Mon, 24 Sep 2012 08:42:48 GMT

It's probably easiest if you could post a small sample (app or xls or inline data) to the forum, together with your expected outcome.

Do you want to calculate the expression in a chart (with what dimensions?) or in a text object?

How are the fields involved connected to each other?

Re: count / sum Problem

Mon, 24 Sep 2012 09:18:21 GMT

it a chart.

the formula creats the vaules you can see.

for example the last one 142 -> count({< MA_BAUREIHE = {'ELION','ELION_HYBRID','EVOS'} >} distinct BI_VERTRIEBSBELEG_NR&BI_VERKAUFSBELEG_POS )

the data looks like this:

but now i have 1 dimenson more BI_MENGE

Re: count / sum Problem

swuehl — Mon, 24 Sep 2012 10:12:43 GMT

I assume you are using one dimension right now, a Date dimension, right?

Since you are doing a distinct count of BELEG_NR & BELEG_POS, I also assume that you could have multiple occurences of the same BELEG_NR & BELEG_POS on a single day, is this correct?

If so, could you also have different BI_MENGE for these records with identical BELEG_NR & BELEG_POS?

And how do you want to use the BI_MENGE then in your calculation? I am still missing an example with your expected outcome.

Re: count / sum Problem

Mon, 24 Sep 2012 15:11:32 GMT

swuehl schrieb:
I assume you are using one dimension right now, a Date dimension, right?
--> yes
Since you are doing a distinct count of BELEG_NR & BELEG_POS, I also assume that you could have multiple occurences of the same BELEG_NR & BELEG_POS on a single day, is this correct?
--> not a single day - but the same records over different months
If so, could you also have different BI_MENGE for these records with identical BELEG_NR & BELEG_POS?
And how do you want to use the BI_MENGE then in your calculation? I am still missing an example with your expected outcome.
--> yes the bi_menge can be different and shod be taken from the last run_date

please have a look in to my test.qvw.

thanks alot for your effort !

ben

Re: count / sum Problem

swuehl — Mon, 24 Sep 2012 15:28:06 GMT

I think we are getting closer. Your expression could probably look like

=sum(aggr(FirstSortedValue(BI_MENGE,- KEY_LAUFDATUM_DATUM),BI_VERTRIEBSBELEG_NR))

This assumes that BELEG_NR is a valid dimension here in your case (in your sample, POS_NR was always identical).

But in case you need the POS_NR, I think you need to create a field in your script defining your distinct dimension (you can't use a calculated dimension in the aggr() function, unfortunately) and then use this field as aggr() dimension.

Re: count / sum Problem

Mon, 24 Sep 2012 15:47:16 GMT

thats looking really well !

I need the POS_NR - it's not always 0100. i will make a new field with BELEG_NR & POS_NR.

I am back tomorrow

thanks al lot

Re: count / sum Problem

Wed, 26 Sep 2012 06:55:30 GMT

Hi Stefan

Now it's working nice !

Thanks a lot for your Help !

Regards

Beny