Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Friends,
In my data base there are many products under 'CLA_CODE', within this cla_codes there a many sub products and Those are named as 'PRD_CODE'
Ex:- CLA_CODE PRD_CODE
PP SE
PP SA
PP DD
PP LI
MC 1A
MC 1B
MC 1C Like that,
In 'DD','SE','SA' and 'LI' Prd_code there are different premiums(prices) which are entitled different Commission amounts to the respective marketing force. So what i did to calculate the relevant commission according to the price level, i introduced lower and upper limits to the price and commission structure(table) was built.
Then i have build Four arguments in my basic formula to calculate 'DIRECT_ME',
IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0 AND CLA_CODE='PP' AND (COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2),(ME_COM),0)
1st , 2nd, 3rd arguments are in order(expected output is coming)
4th one is '(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2)'.
Even though expression got 'OK',This is not working as per my testing. Dear friends, please help me to find the mistake i have done or propose an alternative way to do this.
Sample file attached herewith for your easy reference,
Rgds,
Priyantha.
Finlay i found the longest way to do this.
Thanks every one who shared the knowledge with me. I was in a requirement of doing this in short and easy way.
But i realized that everything can not be done in that manner.
IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0), IF(PRD_CODE='DD' AND GRO_PRE>1500 AND GRO_PRE<2050,225,IF(PRD_CODE='DD'AND GRO_PRE>2051 AND GRO_PRE<3000,250,IF(PRD_CODE='SA'AND GRO_PRE>100 AND GRO_PRE<150,20,IF(PRD_CODE='SA'AND GRO_PRE>475 AND GRO_PRE<600,60,IF(PRD_CODE='PH',100,IF(PRD_CODE='CH',30,IF(PRD_CODE='LI'AND GRO_PRE>475 AND GRO_PRE<600,60,IF(PRD_CODE='LI'AND GRO_PRE>700 AND GRO_PRE<1200,125,IF(PRD_CODE='LI'AND GRO_PRE>1201 AND GRO_PRE<1800,175,IF(PRD_CODE='SE' AND GRO_PRE>500 AND GRO_PRE<800,50,IF(PRD_CODE='SE'AND GRO_PRE>1100 AND GRO_PRE<1500,90,IF(PRD_CODE='SE'AND GRO_PRE<1501 AND GRO_PRE>1900,130,IF(PRD_CODE='PD',COM_PRE*ME_COM,0))))))))))))),0)
Rgds,
Priyantha.
Urgent Please...............!!!
Hi,
What is your expected output?
Pls change condition as or in your expression and try.
IF(CAT='BR' or CLA_CODE='PP' or RANGE1<PREMUM And PREMUM<RANGE2,SUM(BR_COM),0)
Dear Crishna,
I want to match the three arguments at the same time. Then 'OR' is not supported instead of 'AND'.
The other thing is 'PREMUM' should be within the this range 'RANGE1<PREMUM And PREMUM<RANGE2'. problem is this part not working in the formula even the status of the expression 'OK'.
Anyway Thanks lot for the help.
Rgds,
Priyantha.
Use IntervalMatch !!
Please give some information about IntervalMatch as i'm bigginer in QV.
Please notice that the OK in your expression editor is just a syntax validation. It does not indicate any thing about the logic in your expression, and reasonably QlikView can not determine if an expression is inline with your intentions.
For troubleshooting you need to minimize your scenario. It is simply not feasible to trouble shoot a expression with this many parameters, and also you need to break the logic down to a level where you can validate that the different components return the values you expect.
If your problem is related to the expression below, and you have identified that the bold part is the problem, you should provide a sample focusing only on the troubled part.
IF((RANGESUM(BRO_ORC+AGE_ORC+GRO_ORC)=0 AND ORC_COM> 0 AND CLA_CODE='PP' AND(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2),(ME_COM),0)
In your chart add a new expression with the troubled part. Is the result in this column as you expect now?
(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2
If the result is not as expected, continue breaking the expression down in parts, to see where the problem comes from. Does the below to expressions return the result you expect?
(COM_PRE+SR+TC)>RANGE1
(COM_PRE+SR+TC)<RANGE2
Keep on breaking up the trouble expression until you find the logical problem, and then put everything back together piece by piece.
I think I have already recommended in a previous case that you need to provide a very precise example and scenario if you expect quick help. Your questions also need to have a proper subject so that community members can see what you need help with, the subject "help" is not very likely to attract the right person to read your question.
If you are a beginner it is recommend that you refer to the reference manual and the help (press F1 in desktop client) for details on functions and features. Have you read the IntervalMatch section and looked at the example?
Dear Tony,
Thanks for the instructions and information.
I have done this and identified "(COM_PRE+SR+TC)>RANGE1 AND (COM_PRE+SR+TC)<RANGE2 " not working.
In my data base COM_PRE+SR+TC=GRO_PRE.
Because i have same Product codes with different prices i have created upper and lower price levels in my commission table and tried to work out the commissions that are entitled with the price levels.
But still i'm not in a position to find out what is the reason to not match with above logic.
Anyway thanks lot once again for your kind help.
Just i'm looking the help menu,
Rgds,
Priyantha.