Skip to main content
Announcements
Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! REGISTER TODAY and save!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Help - Between Function and Expressions

Hi

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

Jo

9 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
swuehl
MVP
MVP

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

Not applicable
Author

Hi

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

Jo

Not applicable
Author

Hi

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

Jo

kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
Not applicable
Author

Hi

Pls see below for example;

AccMan   GP (£)   Sales £)   GP (%)   0-50   50-100   100+   Negative  
1361 3466 39.3  
FD-16-16100.0100 
GF7870011.111.1 
GH200180111.1111.1 
JH36469452.452.4 
PO21629573.273.2
ZX519161332.232.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

Jo

swuehl
MVP
MVP

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

Not applicable
Author

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

Jo

brijesh1991
Partner - Specialist
Partner - Specialist

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