Set Analysis Between values Help!

[#1]

Hi

I am trying set analysis to create dynamic expressions for profit groups. Basically I am after an expression to show where the profit amount for the selections is between the various values (£)  ie 0.-50.00, 50-100

I cannot do this in the script and I need this to be dynamic -  the table will have a cyclic group so the costs can be grouped all different levels ie store, acc man, Month. 

I have tried different variations of the below to no avail- can someone please have a look an advise where im going wrong.

0-50 (£) -- sum ({< GrossProfit= {">=00.00 and <=50.00"}>} GrossProfit )

50-100 (£) -- sum ({< GrossProfit= {">=50.00 and <=100.00"}>} GrossProfit )

Also

Sum ({$<GrossProfit={">0.00<50.00"}>}GrossProfit)

Thanks

Jo

[#2]

Hi,

Try this,

sum ({< GrossProfit= {">=Money(00.00)  <=Money(50.00)"}>} GrossProfit )

Regards,

Kabilan K.

[#3]

Try:

sum ({< GrossProfit= {">=00.00<=50.00"}>} GrossProfit )

Also you can drop the comma separator and decimals:

sum ({< GrossProfit= {">00<=50"}>} GrossProfit )

[#4]

  Thankyou for your replies - I have tried them all and none work;

sum ({< GrossProfit= {">=Money(00.00) <=Money(50.00)"}>} GrossProfit )- gives no results

sum ({< GrossProfit= {">=00.00<=50.00"}>} GrossProfit ) - Doesnt work still returning values above 50.00

sum ({< GrossProfit= {">00<=50"}>} GrossProfit )- Doesnt work still returning values above 50.00

Jo

[#5]

sum ({< Num(GrossProfit)= {">00<=50"}>} GrossProfit )

[kaushiknsolanki]

Hi,

     Make sure that your GrossProfit contains the number format.

     Try this in your script.

     Load num#(GrossProfit) as GrossProfit.....

     And then your expression should work.

     If this doesnt work also, kindly post your application.

Regards,

Kaushik Solanki

[narender123]

Hi,

Try this

=sum(if(GrossProfit>=0 and GrossProfit<=50,GrossProfit))

Thanks

Narender

[#8]

Thanks

Kabilian - these was an error in the expression so wouldnt run

Kaushik - I will try this now and let you know

Narender - I am avoiding If statements and trying to complete this within set analysis due to size of the table - when If statements are used I get Out of Object Memory errors.

Many thanks for all your responses much appreciated

Jo

[#9]

Thankyou Kaushik  this appears to have worked but I am getting crossovers where the data is appearing in two columns instead of the one. ie -16 value is showing in the 0-50 and 50-100 colums as well as the negative.

I have attached sample QV for your inspection

[kaushiknsolanki · Accepted solution]

Hi,

     Have a look at the application.

     I have chaged the expressions.

Regards,

Kaushik Solanki