topic Set analysis unknown error in QlikView

Set analysis unknown error

Tue, 22 Sep 2015 06:31:50 GMT

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

Re: Set analysis unknown error

PrashantSangle — Tue, 22 Sep 2015 06:37:58 GMT

Hi,

Few points need to check

1: Check your brackets for last condition

Is it (count(ActivityID)/sum(Activity_Target)-1)

(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

Re: Set analysis unknown error

Tue, 22 Sep 2015 06:46:25 GMT

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 06:51:06 GMT

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 06:58:02 GMT

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 07:00:08 GMT

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 07:25:35 GMT

Thanks ,

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 07:38:02 GMT

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

Re: Set analysis unknown error

Tue, 22 Sep 2015 09:05:08 GMT

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