topic Re: expression to be calculated based on the selections in QlikView

expression to be calculated based on the selections

Thu, 10 Apr 2014 14:05:08 GMT

Depending upon particular selections i want to add $100 to the Forecast column. Selections that need to be are

Center='RO'

PD='other'

month=12

then sum(forecast) +100 else sum(forecast)

having little bit of difficulty implementing this in the expression so i made a prototype to mimic my requirement.

Thanks in advance.

PFA

Re: Help with expression

Thu, 10 Apr 2014 14:22:54 GMT

i did try to use this expression

=If((GetFieldSelections(Center)='RO' and PD='other' and CM_Month='12'),(sum(Forecast)+100),Sum(Forecast))

but the issue is when i try to multi pick the month values like 10, 11 & 12 the expression doesn't work the way needed.

Now, the issue have been narrowed down to when i multi pick the month or when ever the month field value has a 12 then for PD=other there should be +100, how can i do this?

Re: expression to be calculated based on the selections

MK_QSL — Thu, 10 Apr 2014 14:49:38 GMT

IF(GetFieldSelections(Center)='RO' and PD = 'other' and GetFieldSelections(CM_Month)=12, SUM(Forecast)+100,SUM(Forecast))

Re: Help with expression

maxgro — Thu, 10 Apr 2014 14:53:15 GMT

if I understand try (put in a texbox to see the result)

=WildMatch(GetFieldSelections(CM_Month, '@'), '*12*')

=WildMatch(Concat(CM_Month, '@'), '*12*')

instead of

GetFieldSelections('Month', 12)

in your expression

Re: expression to be calculated based on the selections

goncalo_ricardo_pereira — Thu, 10 Apr 2014 14:53:50 GMT

Hello Dinesh,

Try this expression.

=if((GetSelectedCount(CM_Month) > 1 or CM_Month='12') and PD='other',SUM(Forecast)+100,SUM(Forecast))

Re: expression to be calculated based on the selections

Thu, 10 Apr 2014 14:56:21 GMT

Hello Manish, but this doesn't work if i multi pick like months=10, 11 and 12. This only works when my selection is just for the month=12 only.

Re: expression to be calculated based on the selections

rustyfishbones — Thu, 10 Apr 2014 15:01:48 GMT

Does the attached help

Re: expression to be calculated based on the selections

Anonymous — Thu, 10 Apr 2014 15:05:34 GMT

So, the requirement is probably not

month=12

but

month 12 is in selection.

If yes, this would work:

=If((Center='RO' and PD='other' and index(GetFieldSelections(CM_Month),12)>0),(sum(Forecast)+100),Sum(Forecast))

Re: expression to be calculated based on the selections

PrashantSangle — Thu, 10 Apr 2014 15:06:06 GMT

Hi,

Checked enclosed file it is working as per your requirements.

use this expression

=If((Center='RO' and PD='other') and WildMatch(Concat(CM_Month, '@'), '*12*'),(sum(Forecast)+100),Sum(Forecast))

Regards,

Re: expression to be calculated based on the selections

MK_QSL — Thu, 10 Apr 2014 15:13:06 GMT

IF(GetFieldSelections(Center)='RO' and PD = 'other' and Index(GetFieldSelections(CM_Month),12)>0, SUM(Forecast)+100,SUM(Forecast))

Re: expression to be calculated based on the selections

luisvalenzuela — Thu, 10 Apr 2014 15:54:21 GMT

Hi try this,

=RangeSum(

if(

SubStringCount(Concat(Center, '|'), 'RO')>=1 and

SubStringCount(Concat(PD, '|'), 'other')>=1 and

SubStringCount(Concat(CM_Month, '|'), '12')>=1,100,0

)

sum(Forecast)

)

Regards