count / sum Problem

[#1]

Hi

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

[swuehl]

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)

[#3]

Hi

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

what you must now about my data model ?

[swuehl]

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?

[#5]

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

[swuehl]

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.

[#7]

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

[swuehl · Accepted solution]

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.

See also attached.

Stefan

[#9]

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

[#10]

Hi Stefan

Now it's working nice !

Thanks a lot for your Help !

Regards

Beny