Convert Gross Margin % filter to text

[vsap2000]

Hello,

I have Gross Margin which has following formula

GM% = If(Sum(GP)/Sum(Sales) <0,'X<0',if(sum(GP)/Sum(Sales)<20,'0<=X<=20',if(sum(GP)/Sum(Sales)<75,'20<=X<=75','X>75') , I want to use this expression as dimension, when I try to create it gives me Invalid Dimension.Is there any way to convert this to text so I can use as filter on GM% or any other approach is also appreciable.

Thanks in advance.

-vsap2000

[anushree1]

There are missing braces in your expression , please use the below:

If(Sum(GP)/Sum(Sales) <0,'X<0',if(sum(GP)/Sum(Sales)<20,'0<=X<=20',if(sum(GP)/Sum(Sales)<75,'20<=X<=75','X>75')))

Also use it as a dimension in the script.

[rathore01]

Hi,
aggr(If(Sum(GP)/Sum(Sales) <0,'X<0',if(sum(GP)/Sum(Sales)<20,'0<=X<=20',if(sum(GP)/Sum(Sales)<75,'20<=X<=75','X>75'),dimension)
you can use any dimension based on your requirement.

[vsap2000]

Thanks for reply, I tried to use

aggr(If(Sum(GP)/Sum(Sales) <0,'X<0',if(sum(GP)/Sum(Sales)<20,'0<=X<=20',if(sum(GP)/Sum(Sales)<75,'20<=X<=75','X>75'),customer, product) ,it shows the correct bucket, but when I try to filter on anyone it doesn't work correctly, below is eg how it works

Cust Prod Curr GM Last GM Bkt

A      X1        -7.1         9.1           x < 0

A    X2          19.2      34.4         0<=x<=20

A  X3           65.2       45           20<=X<=75

B   X1          25.3      23.14      20<=x<75

B   X4         74           78            x>75

C  X1          -3.1       4.1           x<0

C X2         4.5          6.7          x<=0<=20

so when I select x<0 ,it selects all the customer related to that bucket

I am a getting following result :

Cust Prod Curr GM Last GM Bkt

A      X1        -7.1         9.1           x < 0

A    X2          19.2      34.4         0<=x<=20

A  X3           65.2       45           20<=X<=75

C  X1          -3.1       4.1           x<0

C X2         4.5          6.7          x<=0<=20

how I can get only 1st and 4th row only instead of getting all cust prod combination?

Thanks.

[rathore01]

Hi,

Slight correction on 'if condition' - 

aggr(If(Sum(GP)/Sum(Sales) <= 0,'X<0',
(if(Sum(GP)/Sum(Sales) > 0 and sum(GP)/Sum(Sales) <= 20,'0<=X<=20',
(if(sum(GP)/Sum(Sales) > 20 and sum(GP)/Sum(Sales) <=75,'20<=X<=75',
'X>75'))))),Cust,Prod)

Please share Sales and GP sample data to get accurate results.

Thanks

[vsap2000 · Accepted solution]

Hi,

When I tweaked it as shown below it works very well.

If(Aggr(Sum(GP)/Sum(Sales), Cust,Prod) <= 0,'X<0',

             if(Aggr(Sum(GP)/Sum(Sales) < 0.2,Cust,Prod), '0<=X<20',

               if(Aggr(Sum(GP)/Sum(Sales) < 0.75, Cust,Prod),'20<=X<75',

                  'X>75')))

Thanks.