topic Re: IF AND OR statement in set analysis in App Development

IF AND OR statement in set analysis

julian — Mon, 20 Dec 2021 21:08:46 GMT

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

Re: IF AND OR statement in set analysis

Lauri — Mon, 27 Apr 2020 14:16:27 GMT

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))

)

Re: IF AND OR statement in set analysis

julian — Tue, 28 Apr 2020 08:56:37 GMT

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.

Re: IF AND OR statement in set analysis

agigliotti — Tue, 28 Apr 2020 09:31:42 GMT

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.

Re: IF AND OR statement in set analysis

julian — Tue, 28 Apr 2020 10:10:21 GMT

Unfortunately, that just returns a zero

Re: IF AND OR statement in set analysis

Lauri — Tue, 28 Apr 2020 11:03:15 GMT

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.

Re: IF AND OR statement in set analysis

julian — Tue, 28 Apr 2020 11:29:29 GMT

That just returns 0.1342... for all articles

Re: IF AND OR statement in set analysis

Lauri — Tue, 28 Apr 2020 11:44:14 GMT

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).

Re: IF AND OR statement in set analysis

julian — Tue, 28 Apr 2020 11:53:24 GMT

Just checked and yes that is what is happening

Re: IF AND OR statement in set analysis

Lauri — Wed, 29 Apr 2020 15:11:13 GMT

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.

Re: IF AND OR statement in set analysis

julian — Thu, 30 Apr 2020 08:42:19 GMT

I finally managed to figure it out! The problem with the previous one was that instead of having EAV as Else I just needed to replace it with a 1 because it was preceded with / it divides it with the Else statement so replacing it with the 1 means you get the original EAV calculation. Thank you so much for your help Lauri I couldn't have solved it without it. Here is the correct calculation:

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

If([MediaTypeName]= '**bleep**blade og magasiner'AND [MediaCountryName] = 'Danmark' ,7*([Størrelse]),

If([MediaTypeName] = '**bleep**blade og magasiner' AND Match([MediaCountryName], 'Sverige', 'Norge'), 2*([Størrelse]), 1))