topic Re: avoiding nested if condition in QlikView

avoiding nested if condition

berryandcherry6 — Wed, 11 May 2016 10:59:30 GMT

I have a formula it is built using if statements,but it looks lenghty and confusing as its size increases.Is there any other work for this.

Num(if(Subjects='Economics',Count({$<DateType = {applied},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id),

if(Subjects='computer',count({$<DateType = {approved},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id),

if(Subjects='science',sum({$<DateType = {attended},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}attended_id),

if(Subjects='Completions',sum({$< DateType = {completion},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>}course_completed),

if(Subjects='Completions Rate',sum({$< DateType = {completion},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>}course_completed)/

Count({$<DateType = {invitation},school_name={'matais'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id)

))))),if(Subjects='Completions Rate','#,##0.0%', '#,##0'))

Please help me to achieve this.

Regards,

Supriya

Re: avoiding nested if condition

settu_periasamy — Wed, 11 May 2016 18:36:04 GMT

May be you create a variable and assign this expression. then you can simply write the expression like

=Num($(vLongExpression),if(Subjects='Completions Rate','#,##0.0%', '#,##0'))

Re: avoiding nested if condition

sunny_talwar — Wed, 11 May 2016 20:17:10 GMT

Can you try this:

Num(

Alt(

Count({$<DateType = {applied}, school_name={'matais'}, Subjects= {'Economics'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>} applied_id),

Count({$<DateType = {approved}, school_name={'matais'}, Subjects={'computer'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>} applied_id),

Sum({$<DateType = {attended}, school_name={'matais'}, Subjects = {'science'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>} attended_id),

Sum({$< DateType = {completion}, school_name={'matais'}, Subjects = {'Completions'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>} course_completed),

Sum({$< DateType = {completion},school_name={'matais'}, Subjects = {'Completions Rate'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>} course_completed)/

Count({$<DateType = {invitation},school_name={'matais'}, Subjects = {'Completions Rate'},

CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>} applied_id)

If(Subjects = 'Completions Rate', '#,##0.0%', '#,##0'))

I am assuming that you are using Subject as your dimension, because if you are not, then you might not see good results

Re: avoiding nested if condition

MarcoWedel — Wed, 11 May 2016 21:08:37 GMT

Hi,

maybe also possible:

=Num(Pick(Match(Subjects,'Economics','computer','science','Completions','Completions Rate'),
          Count({$<DateType = {applied}   ,school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id),
          Count({$<DateType = {approved}  ,school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id),
          Sum(  {$<DateType = {attended}  ,school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}attended_id),
          Sum(  {$<DateType = {completion},school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>}course_completed),
          Sum(  {$<DateType = {completion},school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"},final_test_status={'passed'}>}course_completed)/
          Count({$<DateType = {invitation},school_name={'matais'},CanonicalDate = {">=$(=MinDate)<=$(=max(date(CanonicalDate)))"}>}applied_id)
          ),
    '#,##0'&If(Subjects='Completions Rate','.0%')   
    )

I agree that a variable approach might be better though..

hope this helps

regards

Marco

Re: avoiding nested if condition

berryandcherry6 — Thu, 12 May 2016 18:21:52 GMT

Hi stalwar1 ,

Thanks for reply

given expression when used gives first counts for all rows.i am using table and taking subject as dimension.

Regards,

Supriya

Re: avoiding nested if condition

sunny_talwar — Thu, 12 May 2016 18:23:30 GMT

How about this:

Num(

RangeSum(