Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi ,
I am working on a expression to calculate a value for a belwo mentioned condition
if count(activityid) > sum(activitytarget)
then it should be doing( (a/b)-1) to calculate the percentage .
We are showing these value at YTD , MTD and monthly level .
I have created two boxes when YTD is selected the value comes right .
When MTD is selected is fine , but when I select an individual month, the expression doesn't calculate the value .
Expression used Is mentioned below
=if(GetSelectedCount(Month)=0,
if((count({<Month={'$(currMonthName)'}>}ActivityId))>(sum({<Month={'$(currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(currMonthName)'}>}ActivityId))/(sum({<Month={'$(currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'),
num((count(ActivityID)/sum(Activity_Target)-1),'#,##0.0%')))
the expression doesn't show any error .
I am attaching a sample script with expression .
If I make a selection for year 2014 and month July it should return some 12.## % .
thanks for the help
Best
Medha
Try this expression:
=if(GetSelectedCount(Month)>0,
if((count({<Month={'$(=currMonthName)'}>}ActivityId))>(sum({<Month={'$(=currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(=currMonthName)'}>}ActivityId))/(sum({<Month={'$(=currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'),
num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%')))
It returns -26.1%
Thanks
Hi,
Few points need to check
1: Check your brackets for last condition
Is it (count(ActivityID)/sum(Activity_Target)-1)
or
(count(ActivityID)/sum(Activity_Target))-1
2: use double quote instead of single quote
3: Try by writing = when you are executing variable
like $(=currMonthName)
Regards
hI,
You have used if(GetSelectedCount(Month)=0,...then how will it calculated at expression level,here only conditions fails.
IfGetSelectedCount(Month)>0 satisfies with your requirement
Hi khushboo,
Thanks for prompt help however my condition is
If GetSelectedCount(Month)=0 , then the expression calculate the value till currentmonth ,
therwise it need to calculate the value for the month being selected
.
Try this expression:
=if(GetSelectedCount(Month)>0,
if((count({<Month={'$(=currMonthName)'}>}ActivityId))>(sum({<Month={'$(=currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(=currMonthName)'}>}ActivityId))/(sum({<Month={'$(=currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'),
num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%')))
It returns -26.1%
Thanks
Ok if that is the requirement use this expression,
=if(GetSelectedCount(Month)=0,
if((count({<Month={'$(=currMonthName)'}>}ActivityId))>(sum({<Month={'$(=currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(=currMonthName)'}>}ActivityId))/(sum({<Month={'$(=currMonthName)'}>}Activity_Target)))-1),'#,##0.0%')),
num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%'))
Thanks
Khushboo
Thanks ,
However when we select MTD now the expression doesn't work. 😞
TT DOES NOT WORK BECAUSE THE CONDITION DOESNT SATISFY AND THERE IS NOTHING WRITTEN IN ELSE THERE,
TRY THIS:
=if(GetSelectedCount(Month)=0,
if((count({<Month={'$(=currMonthName)'}>}ActivityId))>(sum({<Month={'$(=currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(=currMonthName)'}>}ActivityId))/(sum({<Month={'$(=currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'),
num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%')),
num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%'))
THANKS
KHUSHBOO
Thanks A lot Khushboo ,
Ur help is much appreciated .
i just tweaked the expression and got the desired answer .
For your ref this worked perfectly .
=if(GetSelectedCount(Month)=0,
if((count({<Month={'$(currMonthName)'}>}ActivityId))>(sum({<Month={'$(currMonthName)'}>}Activity_Target)),
num((((count({<Month={'$(currMonthName)'}>}ActivityId))/(sum({<Month={'$(currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'),
num((((count({<Month={'$(currMonthName)'}>}ActivityId))/(sum({<Month={'$(currMonthName)'}>}Activity_Target)))-1),'#,##0.0%'))
,num(((count(ActivityId))/(sum(Activity_Target)))-1,'#,##0.0%'))