Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Become an analytics expert with Qlik's new 15 week course: Applied Data Analytics using Qlik Sense. READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis Between values Help!

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

1 Solution

Accepted Solutions
kaushiknsolanki
Luminary Alumni
Luminary Alumni

Hi,

     Have a look at the application.

     I have chaged the expressions.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

13 Replies
Not applicable
Author

Hi,

Try this,

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

Regards,

Kabilan K.

Not applicable
Author

Try:

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

Also you can drop the comma separator and decimals:

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

Not applicable
Author

  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

Not applicable
Author

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

kaushiknsolanki
Luminary Alumni
Luminary Alumni

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".
narender123
Specialist
Specialist

Hi,

Try this

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

Thanks

Narender

Not applicable
Author

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

Not applicable
Author

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
Luminary Alumni
Luminary Alumni

Hi,

     Have a look at the application.

     I have chaged the expressions.

Regards,

Kaushik Solanki

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post