Set analysis unknown error

[#1]

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

[PrashantSangle]

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

[#3]

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

[#4]

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

.

[#5 · Accepted solution]

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

[#6]

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

[#7]

Thanks ,

However when we select MTD now the expression doesn't work. 😞

[#8]

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

[#9]

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