avoiding nested if condition

[berryandcherry6]

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

[settu_periasamy]

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

[sunny_talwar]

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

[MarcoWedel]

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

[berryandcherry6]

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

[sunny_talwar]

How about this:

Num(

RangeSum(

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

[berryandcherry6]

Hi settu_periasamy ,

Thanks for reply

LET vLongExpression =

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)

)))))  

above expression doesnot load in script loader ,gives script line error. How should i use this code in script.

Please help me in this.

Regards,

Supriya

[berryandcherry6]

Hi,

same values repeats in every row of table.Actually Rangesum returns sum of range of values present in it.How could we use it here.Correct me if i am wrong.

Regards,

Supriya

[sunny_talwar]

Because of the set analysis, it will only have a value for each dimension. Not sure if you noticed this:

Num(

RangeSum(

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