Mit SetExpression (SetAnalysis) festlegen das nur Werte genommen werden sollen, wenn Datum und Datum2 gleich sind

[merry2018]

Hallo Zusammen,

ich suche eine Set Analysis Formel, welche mir nur die Werte summiert, die das selbe Datum in dem Datumsfeld 1 und zwei haben.

Um es besser zu verstehen was ich will, hier eine Beispieltabelle:

Sparte	%KEY-Artikel	Menge	Freitag	%Key_Datum
Teil	A	4700	02.03.2018	01.03.2018
Teil	A	5000	02.03.2018	01.03.2018
Teil	A	5000	02.03.2018	02.03.2018
Teil	A	4700	09.03.2018	09.03.2018
Teil	A	5000	09.03.2018	10.03.2018
Teil	B	250	09.03.2018	09.03.2018
Teil	B	250	09.03.2018	10.03.2018
Teil	B	250	09.03.2018	11.03.2018

Und zwar ist das Datum bei Freitag und %Key_Datum das selbe, also soll es mir folgendes anzeigen:

Teil

4950

09.03.2018

Dieses habe ich mit folgenden Formeln versucht:

if(Floor(%Key_Datum)=Floor(Freitag),Menge,0)

SUM(aggr(<%Key_Datum_Kalender={'Freitag'}>sum(Rückstand.Menge),%Key_Datum_Kalender))

aber keine der beiden zeigt an was ich möchte. Die erste gibt es mir nur aus, wenn ich die betreffenden Artikel auswähle, und bei der zweiten stimmt vermutlich die Bedingung nicht ganz, denn diese gibt noch Garnichts aus.

Ich wäre sehr Dankbar über eine Hilfe von euch!

[petter]

Min() can be replaced by Only() since we are filtering on the row level here with a row level counter called R# and generated in the script with RowNo().

[merry2018]

Thanks for the Answer.

I don't have a RowNo but I have a Key for the "Artikel"

It's working, if I have one Table with all Information, but if the Date "Freitag" is from a secound Table, it doesn't work?

Did you have a explain for that or a solution how it work with two tables?

My example with two tables are the following:

Key	Sparte	Artikel	Menge	%Key_Datum
1	Teil	A	4700	01.03.2018
2	Teil	A	5000	02.03.2018

Freitag	%Key_Datum
02.03.2018	01.03.2018
02.03.2018	02.03.2018

What does the only different from the Min Function?

[marcus_sommer]

I'm not sure that it will be possible with just a set analysis else because of the fact that the data are in two tables you might need an aggr-function to merge these tables and then using some if-conditions and/or set analysis on this basis.

Quite probably I would use a different approach by creating an appropriate flag-field within the script, maybe with something like this:

map: mapping load %Key_Datum, Freitag from SourceA;

table: load *, -(applymap('map', %Key_Datum, 0)=%Key_Datum) as Flag from SourceB;

and your UI expression would look like: sum(Menge*Flag)

- Marcus

[merry2018]

if there is another way, I don't have to use set Analysis.

How you mean I can do with aggr-function, because the Table should be separated, if it is possible.

[marcus_sommer]

An aggr might be look like:

sum(aggr(if(Freitag=%Key_Datum, Menge), Freitag, %Key_Datum))

The above mentioned flag-approach doesn't mean mandatory that both tables are respectively should be merged else it just adds an extra column to simplify the UI expression and speed them up (by small amounts of data it won't be very relevant but by large datasets aggr-functions and if-loops could slow down the performance quite heavily).

- Marcus

[merry2018]

Your Aggr-Function do not work, the result is "0".

In the table-diagramm i only have the Feld %Key_Datum and Artikel together with the aggr-function as a Formel.

[marcus_sommer]

You could also play with something like this:

sum(aggr(if(Freitag=%Key_Datum, Menge), Freitag, %Key_Datum, Menge))

sum(aggr(if(Freitag=%Key_Datum, sum(Menge)), Freitag, %Key_Datum))

But like mentioned I would rather prefer the flag-approach.

- Marcus

[petter]

I don't see why it should matter that Freitag is in a separate table. The tables would be associated with a key field right? So then it should work well.

Only() is a (pseudo) aggregation function that will only return something if there is a single value to return else it will return NULL. So you have to be sure that the dimensions involved will return a single row/value - which is the case as long as you use a unique row identifier like I created. You can of course use any other row identifier in the table where you have the field being aggregated.

[petter]

You can also leave out the aggregation function as long as you are absolutely sure that there will be a single row returned like this:

Sum( {<R#={"=Freitag=%Key_Datum"}>} Menge )