- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
avoiding nested if condition
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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'))