Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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'))
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
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
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
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'))
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
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
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'))