~ exclude how it will work in expression

[Karim_Khan]

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,

KK

[sasiparupudi1]

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])

[kaushiknsolanki]

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

[tresesco]

Try '-' for exclusion like:

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

[Karim_Khan]

still showing the same error

[Karim_Khan]

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 %.

	Sales Level 2 Customer Count Bookings % Bookings Avg Deal Size Total 7424 222457000.8 9.41% 33225.42844 CANADA 631 20932019.92 9.41% 33225.42844 LATIN AMERICA 741 29788670.25 9.41% 33225.42844 US COMMERCIAL 4098 58816212.46 9.41% 33225.42844 US ENTERPRISE 549 51737137 9.41% 33225.42844 US OTHER 3 5475872.92 9.41% 33225.42844 US PS MARKET SEGMENT 1402 55707088.24 9.41% 33225.42844

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

=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,

KK

[Karim_Khan]

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 %.

	Sales Level 2 Customer Count Bookings % Bookings Avg Deal Size Total 7424 222457000.8 9.41% 33225.42844 CANADA 631 20932019.92 9.41% 33225.42844 LATIN AMERICA 741 29788670.25 9.41% 33225.42844 US COMMERCIAL 4098 58816212.46 9.41% 33225.42844 US ENTERPRISE 549 51737137 9.41% 33225.42844 US OTHER 3 5475872.92 9.41% 33225.42844 US PS MARKET SEGMENT 1402 55707088.24 9.41% 33225.42844

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

=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])

[Karim_Khan]

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 %.

	Sales Level 2 Customer Count Bookings % Bookings Avg Deal Size Total 7424 222457000.8 9.41% 33225.42844 CANADA 631 20932019.92 9.41% 33225.42844 LATIN AMERICA 741 29788670.25 9.41% 33225.42844 US COMMERCIAL 4098 58816212.46 9.41% 33225.42844 US ENTERPRISE 549 51737137 9.41% 33225.42844 US OTHER 3 5475872.92 9.41% 33225.42844 US PS MARKET SEGMENT 1402 55707088.24 9.41% 33225.42844

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

=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])

[sasiparupudi1]

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?

[Karim_Khan]

I am getting the red line error.Its showing error