Pivot Table ->Use a MAX(Value) within an expression

[JR_38]

Hi all,

I’m stuck on one point while working with a pivot table.

I created the following dimension:

=AGGR(MAX([Item'LANDED']), [Item Barcodes],[Stock-Bez])

The expected price is displayed correctly in the pivot table (the MAX price grouped by Barcode and Stock-Bez).
In my example, the value is 51,077€.

Based on the same logic, I would now like to use this value inside an expression that includes two conditions:

• only when MATCH([BESTAND RECHNEN METHODE], 'BIS-DATUM')
• AND DATE(Datum) <= MONTHEND(TODAY(1), -1)

However, when I try to use the same AGGR logic inside my expression, the result is always 0.

=SUM( IF(MATCH([BESTAND RECHNEN METHODE],'AM-DATUM') AND MATCH(DATE(Datum),MONTHEND(TODAY(1),-1)), [Menge BES (Bestand)] * [Artikel 'LANDED'],

      IF(MATCH([BESTAND RECHNEN METHODE],'BIS-DATUM') AND DATE(Datum) <= MONTHEND(TODAY(1),-1),[Menge BES (Bestand)] *  ( AGGR(MAX([Artikel 'LANDED']), [Artikel Barcodes])) 

      )))

I tried applying a MAX on the result of the AGGR expression
MAX( AGGR(MAX([Artikel 'LANDED']), [Artikel Barcodes], [Lager-Bez]) )
but that didn’t work either.

Do you have any idea how I could solve this issue? Or maybe another approach to get the correct result?

[marcus_sommer]

You may consider not to nest both logic else to add them in row, like:

rangesum(sum({ Set1 } Menge * Wert), sum({ Set2 } Menge * Wert))

Further I could imagine that the aggr() stuff may rather look like:

sum(
  AGGR(
   IF(MATCH([BESTAND RECHNEN METHODE],'BIS-DATUM') AND DATE(Datum) <= 
   MONTHEND(TODAY(1),-1),
   sum([Menge BES (Bestand)]) *  MAX([Artikel 'LANDED'])),
  [Artikel Barcodes])) 

[JR_38]

HI Marcus, thanks for your reply and your help.

Unfortunatelly the aggr() attempt resulted in 0 again.

I think I need to resolve the issue within the scripting and not in the diagramm.

[marcus_sommer]

I agree to transfer all essentiell logic into the data-model. Even if it's possible to get the needed stock and price information in the UI with conditional queries within (nested) aggregations the efforts and the complexity of such approaches are usually much too high to be sensible (only within a single view - but not if n different views against n different dimensions/selections are needed).