IF AND OR statement in set analysis

[julian]

Hi there

If have this calculation 

(SUM( AGGR(AVG(EAV), ArticleKey)) / 7.45)

I need to add the following. If [MediaName ]= 'Magazine' AND MediaCountry = 'Denmark' /7 *[ArticleSize] OR [MediaName] = '**bleep**blade & Magasiner' AND [MediaCountry] = Sweden OR Norway /2 *[ArticleSize]

Thanks

[Lauri]

Do you mean you need to change the divisor from 7.45 to something else depending on the IF statement?  You can put an IF statement in like this:

(SUM( AGGR(AVG(EAV), ArticleKey)) /

If([MediaName]= 'Magazine' AND MediaCountry = 'Denmark', 7*[ArticleSize],

If([MediaName] = '**bleep**blade & Magasiner' AND Match([MediaCountry], 'Sweden', 'Norway'), 2*[ArticleSize])

)

However, if ArticleSize is at the same detail level as ArticleKey, you won't get logical results (or maybe any results) because you are dividing an aggregate SUM by a more detail level number. So you would need to also aggregate your divisor, perhaps with the same SUM(AGGR(AVG())), which would look like:

(SUM( AGGR(AVG(EAV), ArticleKey)) /

SUM( AGGR(AVG(If([MediaName]= 'Magazine' AND MediaCountry = 'Denmark', 7*[ArticleSize],

If([MediaName] = '**bleep**blade & Magasiner' AND Match([MediaCountry], 'Sweden', 'Norway'), 2*[ArticleSize])), ArticleKey))

)

[julian]

Hi Lauri

Thank you for your suggestion. I still need the 7.45 divisor as this is used to get the price in euros. Using what you suggested the following:

SUM( AGGR(AVG(EAV), ArticleKey))/7.45 /

If([MediaName]= 'Magazine'AND MediaCountry = 'Denmark' ,7*([ArticleSize]),

If([MediaName] = '**bleep**blade og magasiner' AND Match([MediaCountry], 'Sweden', 'Norway'), 2*([ArticleSize])))

works but only for Sweden and Norway. For Denmark it is for reason off by a factor 4. Also when using this I only get results for these categories and none of the other where it should show the 'regular' EAV.

[agigliotti]

maybe this:

=SUM( AGGR( AVG(
If( [MediaName] = 'Magazine' AND MediaCountry = 'Denmark', EAV * [ArticleSize] / 7,
if( [MediaName] = '**bleep**blade & Magasiner' AND ( [MediaCountry] = 'Sweden' OR [MediaCountry] = 'Norway' ), EAV * [ArticleSize] / 2 ), EAV
) )
, ArticleKey ) )
/
7.45

I hope it can helps.

[julian]

Unfortunately, that just returns a zero

[Lauri]

Let's add parentheses to make sure the calculations are in the correct order, and also the else condition:

( SUM( AGGR(AVG(EAV), ArticleKey))/7.45 ) /

If([MediaName]= 'Magazine' AND MediaCountry = 'Denmark' ,7*([ArticleSize]),

If([MediaName] = '**bleep**blade og magasiner' AND Match([MediaCountry], 'Sweden', 'Norway'), 2*([ArticleSize]), EAV))

I am guessing that EAV is the right thing to put in as the 'regular' EAV.

[julian]

That just returns 0.1342... for all articles 

[Lauri]

Is 0.1342 what you get if you calculate ( SUM( AGGR(AVG(EAV), ArticleKey))/7.45 ) / EAV? This would tell us that something is wrong with the entire IF statement, and it's always going to the last Else statement (which contains just EAV). 

[julian]

Just checked and yes that is  what is happening

[Lauri]

Have you figured out what is wrong with the If statements? They look ok to me, but I don't know your details well enough to be sure. The other possibility is what I initially said: You need to aggregate the result of your If statement.