Set Analysis Exp help

[abhaysingh]

Hi,

How can i put below condition in set analysis?? thanks

if(FLAG_TXN = 'SALES_PLAN',

if(IsNull(avg({<[G/L Category Flag],[G/L to Consider] ,FLAG_TXN={'SALES_PLAN'}>}AVG_FINAL_BRATE)),'RTBF',avg({<[G/L Category Flag],[G/L to Consider] ,FLAG_TXN={'SALES_PLAN'}>}AVG_FINAL_BRATE)),

if(FLAG_TXN= 'PRODUCTION_VALUE',

if(IsNull(avg({< [G/L Category Flag],[G/L to Consider] ,FLAG_TXN={'PRODUCTION_VALUE'}>}FINAL_AVG_BRATE_MSEG)),'RTBF',avg({<[G/L Category Flag],[G/L to Consider] ,FLAG_TXN={'PRODUCTION_VALUE'}>}FINAL_AVG_BRATE_MSEG)),

if(FLAG_TXN = 'BSEG_SALES',

if(IsNull(avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag]={'SALE'}, [G/L to Consider] ={'Y'},[G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>}FINAL_AVG_BRATE)),'RTBF',

avg({<FLAG_TXN = {'BSEG_SALES'},[G/L Category Flag]={'SALE'}, [G/L to Consider] ={'Y'},[G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>}FINAL_AVG_BRATE)))))

[abhaysingh]

i want to exclude the if condition in it..

[sunny_talwar]

How about this:

RangeSum(

If(IsNull(Avg({<[G/L Category Flag], [G/L to Consider], FLAG_TXN={'SALES_PLAN'}>} AVG_FINAL_BRATE)), 'RTBF', Avg({<[G/L Category Flag],[G/L to Consider], FLAG_TXN = {'SALES_PLAN'}>} AVG_FINAL_BRATE)),

If(IsNull(Avg({<[G/L Category Flag], [G/L to Consider], FLAG_TXN ={'PRODUCTION_VALUE'}>} FINAL_AVG_BRATE_MSEG)), 'RTBF', Avg({<[G/L Category Flag], [G/L to Consider], FLAG_TXN ={'PRODUCTION_VALUE'}>} FINAL_AVG_BRATE_MSEG)),

If(IsNull(Avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag] = {'SALE'}, [G/L to Consider] ={'Y'},[G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>} FINAL_AVG_BRATE)), 'RTBF',

Avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag] = {'SALE'}, [G/L to Consider] = {'Y'}, [G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>} FINAL_AVG_BRATE))

)

[sunny_talwar]

Or this:

RangeSum(

Alt(Avg({<[G/L Category Flag],[G/L to Consider], FLAG_TXN = {'SALES_PLAN'}>} AVG_FINAL_BRATE), 'RTBF'),

Alt(Avg({<[G/L Category Flag], [G/L to Consider], FLAG_TXN ={'PRODUCTION_VALUE'}>} FINAL_AVG_BRATE_MSEG), 'RTBF'),

Alt(Avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag] = {'SALE'}, [G/L to Consider] = {'Y'}, [G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>} FINAL_AVG_BRATE), 'RTBF')

)

[abhaysingh]

HI sunny,

thank for your help,

but i m not getting null values means else condition not fulfilled in this case,, not getting RTBF entries in this..

Pls suggest

[Digvijay_Singh]

Not sure but you can try this small variation as well, took most of the things from Suny's exp -

Pick(Match(FLAG_TXN,'SALES_PLAN','PRODUCTION_VALUE','BSEG_SALES'),

Alt(Avg({<[G/L Category Flag],[G/L to Consider], FLAG_TXN = {'SALES_PLAN'}>} AVG_FINAL_BRATE), 'RTBF'),

Alt(Avg({<[G/L Category Flag], [G/L to Consider], FLAG_TXN ={'PRODUCTION_VALUE'}>} FINAL_AVG_BRATE_MSEG), 'RTBF'),

Alt(Avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag] = {'SALE'}, [G/L to Consider] = {'Y'}, [G/L_Account_No] -= {'0000703401','0000703405','0000703201'}>} FINAL_AVG_BRATE),'RTBF'))

[jonathandienst]

The set condition on FLAG_TXN means that the If is not required. I would try:

Alt(
  Avg({<FLAG_TXN = {'SALES_PLAN'}, [G/L Category Flag], [G/L to Consider]>} AVG_FINAL_BRATE),
  Avg({<FLAG_TXN = {'PRODUCTION_VALUE'}, [G/L Category Flag], [G/L to Consider]>} FINAL_AVG_BRATE_MSEG),
  Avg({<FLAG_TXN = {'BSEG_SALES'}, [G/L Category Flag] = {'SALE'}, [G/L to Consider] = {'Y'}, 
     [G/L_Account_No] -= {'0000703401', '0000703405', '0000703201'}>} FINAL_AVG_BRATE),
  'RTBF'
)

[Digvijay_Singh]

This looks perfect now