topic Re: Set Analysis Exp help in QlikView

Set Analysis Exp help

abhaysingh — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis Exp help

abhaysingh — Thu, 16 Jun 2016 18:05:55 GMT

i want to exclude the if condition in it..

Re: Set Analysis Exp help

sunny_talwar — Thu, 16 Jun 2016 18:50:39 GMT

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

)

Re: Set Analysis Exp help

sunny_talwar — Thu, 16 Jun 2016 18:52:03 GMT

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

)

Re: Set Analysis Exp help

abhaysingh — Tue, 21 Jun 2016 04:37:14 GMT

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

Re: Set Analysis Exp help

Digvijay_Singh — Tue, 21 Jun 2016 05:08:42 GMT

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

Re: Set Analysis Exp help

jonathandienst — Tue, 21 Jun 2016 05:23:03 GMT

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

Re: Set Analysis Exp help

Digvijay_Singh — Tue, 21 Jun 2016 05:31:58 GMT

This looks perfect now