topic Re: Set Analysis Help - Between Function and Expressions in QlikView

Set Analysis Help - Between Function and Expressions

Tue, 23 Jul 2013 09:35:17 GMT

I have with help managed to create a table through set analysis which classifies the GP (£) into groups.

Can the same be done with the table where it classifies the GP (%).

the GP (%) formula is; (sum(GPNUM)/sum(Sales)) *100

I have attempted to calculate this in 0-50.
This cannot be calculated in the script as needs to be dynamic

I have attached the QV for inspection.

Thanks

Re: Set Analysis Help - Between Function and Expressions

kaushiknsolanki — Tue, 23 Jul 2013 09:45:20 GMT

Hi,

You can try this.

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

If this doesnt give proper values then kindly tell us what value you are expecting.

Regards,

Kaushik Solanki

Re: Set Analysis Help - Between Function and Expressions

swuehl — Tue, 23 Jul 2013 10:08:14 GMT

Maybe like attached, using an if() statement to classify your results into the expression buckets?

Re: Set Analysis Help - Between Function and Expressions

Tue, 23 Jul 2013 10:12:38 GMT

Thankyou for your reply.

GP (%) = (sum(GPNUM)/sum(Sales)) *100

I want to create the GP (%) in the expression and then evaluate if it sits under that group the expression is looking at 0-50%.

therefore using the below formula where GPNUM gives the GP (£)

Sum({< GPNUM= {">00.00 and <=50.00"}>} GPNUM )

and modifing it into something like;

Sum ({< ((sum(GPNUM)/sum(Sales)) *100)= {">00.00 and <=50.00"}>} ((sum(GPNUM)/sum(Sales)) *100) )

so the GP % is calculated and then shown in the table column if it sits in that GP (%) Group. so if above GP% is between 0-50% then the GP% value will be brough through on this expression.

I will have other expressions for 50-100, 100+ and Negative values which I can create off the back of this one once the logic is correct. The initially attached QV Test document shows the tablwe layout.

Thanks

Re: Set Analysis Help - Between Function and Expressions

Tue, 23 Jul 2013 10:16:17 GMT

I currently do this with If statements but am moving it into set Analysis due to out of memory object messages.

I am removing all IF statement where possibls to improve speed and processing in my doc.

Thanks

Re: Set Analysis Help - Between Function and Expressions

kaushiknsolanki — Tue, 23 Jul 2013 10:16:34 GMT

Hi,

Do you mean you want to show the group of GP% like 0-10% , 11-30 % and so on.

If yes kindly tell us what will be the expected output for any one group.

Regards,

Kaushik Solanki

Re: Set Analysis Help - Between Function and Expressions

Tue, 23 Jul 2013 10:21:49 GMT

Pls see below for example;

AccMan	GP (£)	Sales £)	GP (%)	0-50	50-100	100+
	1361	3466	39.3
FD	-16	-16	100.0			100
GF	78	700	11.1	11.1
GH	200	180	111.1			111.1
JH	364	694	52.4		52.4
PO	216	295	73.2		73.2
ZX	519	1613	32.2	32.2

I would like the GP(%) to show in the corresponding group so for example

AccMan FD = GP (%) is 100 so 100 would show in the 100+ column

AccMan GF = GP (%) is 11.1 so 11.1 would show in the 0-50 column

Hope this helps

Re: Set Analysis Help - Between Function and Expressions

swuehl — Tue, 23 Jul 2013 10:40:44 GMT

Jo,

attached a version using set analysis. I don't think this version performs much better than the previous one and is probably less readible.

Regards,

Stefan

Re: Set Analysis Help - Between Function and Expressions

Tue, 23 Jul 2013 11:13:10 GMT

This works in the test application however is erroring on the first curly Bracket in my main application.

I am just trying to work out what is causing this.

Thanks

Re: Set Analysis Help - Between Function and Expressions

brijesh1991 — Tue, 23 Jul 2013 11:51:38 GMT

you can check relative check box for every expression to calculate respective %