topic Set Analysis Between values Help! in QlikView

Set Analysis Between values Help!

Mon, 22 Jul 2013 09:22:43 GMT

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

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 09:30:14 GMT

Hi,

Try this,

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

Regards,

Kabilan K.

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 09:57:31 GMT

Try:

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

Also you can drop the comma separator and decimals:

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

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 10:11:55 GMT

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

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 10:17:00 GMT

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

Re: Set Analysis Between values Help!

kaushiknsolanki — Mon, 22 Jul 2013 10:17:42 GMT

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

Re: Set Analysis Between values Help!

narender123 — Mon, 22 Jul 2013 10:24:27 GMT

Hi,

Try this

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

Thanks

Narender

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 10:28:07 GMT

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

Re: Set Analysis Between values Help!

Mon, 22 Jul 2013 11:33:48 GMT

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

Re: Set Analysis Between values Help!

kaushiknsolanki — Mon, 22 Jul 2013 11:39:11 GMT

Hi,

Have a look at the application.

I have chaged the expressions.

Regards,

Kaushik Solanki

Re: Set Analysis Between values Help!

Tue, 23 Jul 2013 08:39:11 GMT

This works perfectly in my QV

Many Thanks

Re: Set Analysis Between values Help!

Tue, 23 Jul 2013 08:54:30 GMT

Thanks

I am trying to use the same logic for groups based on GP (%). Can the below formula be ammended to have;

SUM(GrossProfit)/ SUM(SalesExVat)*100

instead of GPNUM in the below formula

sum ({< GPNUM= {">0<=50"}>} GPNUM)

Thanks

Re: Set Analysis Between values Help!

kaushiknsolanki — Tue, 23 Jul 2013 09:00:26 GMT

Hi,

Sorry I didnt got you.

You can create a new post and post your query with sample data their.

Regards,

Kaushik Solanki

Re: Set Analysis Between values Help!

Tue, 23 Jul 2013 09:31:49 GMT

ok will do thanks