topic Re: ~ exclude how it will work in expression in QlikView

~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 09:11:58 GMT

HI Team,

I wanted to exclude the Sales level 2 value from my Expression but its not working.

=sum({$<~[Sales Level 2]={$(vSel2)},[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]={$(vSel1)}>} [Net Bookings])

Regards,

Re: ~ exclude how it will work in expression

sasiparupudi1 — Wed, 26 Aug 2015 09:13:55 GMT

try like this

=sum({$<[Sales Level 2]-={$(vSel2)},[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]={$(vSel1)}>} [Net Bookings])

Re: ~ exclude how it will work in expression

kaushiknsolanki — Wed, 26 Aug 2015 09:15:13 GMT

Try this,

=sum({$<[Sales Level 2]=,[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]={$(vSel1)}>} [Net Bookings])

This will not consider the Sales Level 2 for the calculation even if you have made any selection for that field.

Regards,

Kaushik Solanki

Re: ~ exclude how it will work in expression

tresB — Wed, 26 Aug 2015 09:17:00 GMT

Try '-' for exclusion like:

<[Sales Level 2] - ={$(vSel2)}

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 12:07:34 GMT

still showing the same error

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 12:12:12 GMT

Hi Kaushik,

I wanted to achieve below Data I was trying to

I wanted to show the Total of the current [Sales Level 1 ] and at the end it should show the sum of All [Sales Level 1].

I will pass the selection on [Sales Level 2].Am able to calculate the first two field.But I am unable to get the %.

As per above Table am able to calculate Customer Count and Bookings.Now I want % Bookings which should show correctly as per

Calculation.

Bookings=Total/Sales Level 2

But its capturing only CANADA value in all Field which is wrong.I want the correct % Bookings value for remaining sales Level 2 also.

E.g :

If I select [Sales Level 1 ] AMERICAS.Then it should show the calculated % bookings value for [Sales Level 2 ] in the above table.

Trying to use expression

Regards,

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 12:12:25 GMT

I wanted to achieve below Data I was trying to

I wanted to show the Total of the current [Sales Level 1 ] and at the end it should show the sum of All [Sales Level 1].

I will pass the selection on [Sales Level 2].Am able to calculate the first two field.But I am unable to get the %.

As per above Table am able to calculate Customer Count and Bookings.Now I want % Bookings which should show correctly as per

Calculation.

Bookings=Total/Sales Level 2

But its capturing only CANADA value in all Field which is wrong.I want the correct % Bookings value for remaining sales Level 2 also.

E.g :

If I select [Sales Level 1 ] AMERICAS.Then it should show the calculated % bookings value for [Sales Level 2 ] in the above table.

Trying to use expression

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 12:12:33 GMT

I wanted to achieve below Data I was trying to

I wanted to show the Total of the current [Sales Level 1 ] and at the end it should show the sum of All [Sales Level 1].

I will pass the selection on [Sales Level 2].Am able to calculate the first two field.But I am unable to get the %.

As per above Table am able to calculate Customer Count and Bookings.Now I want % Bookings which should show correctly as per

Calculation.

Bookings=Total/Sales Level 2

But its capturing only CANADA value in all Field which is wrong.I want the correct % Bookings value for remaining sales Level 2 also.

E.g :

If I select [Sales Level 1 ] AMERICAS.Then it should show the calculated % bookings value for [Sales Level 2 ] in the above table.

Trying to use expression

Re: ~ exclude how it will work in expression

sasiparupudi1 — Wed, 26 Aug 2015 12:14:50 GMT

what is the error you are getting and what are the values in your variables?

may be you can try

sum({$<[Sales Level 2]-={'$(vSel2)'},[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]={'$(vSel1)'}>} [Net Bookings])

or try hardcoding your variable values in the expression and see if it returns values

sum({$<[Sales Level 2]-={'Some value'},[Fiscal Year] = {$(=only([Fiscal Year]))},[Month in Fiscal Year] = {'<=$(=only([Month in Fiscal Year]))'},CATEGORY={'IOT'},[Sales Level 1]={'Some Other value'}>} [Net Bookings])

also

if you put =only([Fiscal Year]) or only([Month in Fiscal Year]) into a text object do you get a value?

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 13:09:45 GMT

I am getting the red line error.Its showing error

Re: ~ exclude how it will work in expression

sasiparupudi1 — Wed, 26 Aug 2015 13:17:50 GMT

the - syntax will show the expression in red but the expression should work fine as long as it is showing expression ok message

hth

Sasi

Re: ~ exclude how it will work in expression

jonathandienst — Wed, 26 Aug 2015 13:22:03 GMT

Qv versions prior to 11.20 SR11 incorrectly flag the -= as an error, but it is valid syntax and it should work. Your use of the ~ was not valid syntax.

If it still does not work, then tell us how vSel1 and vSel2 are defined as these could be causing the error.

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 13:39:54 GMT

ok got it

Re: ~ exclude how it will work in expression

Karim_Khan — Wed, 26 Aug 2015 13:40:01 GMT

ok got it