Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a slider called [Change Regulation] which has 4 selections:
No Change
Small
Medium
Large
Outside of this I have 8 different Occupation types:
AML / KYC Subject Matter Expert
MI Lead
Operations Head
Population Lead
Quality Assurance
Relationship Owner
Review Analyst
Team Lead
These will change based on which selection is made in Change Regulation. So I have set up a variable called vRegulation and attempted to do an if statement for these. This should be how it should look:
No Change = No change for any of the Occupations
Small = Review Analyst & Relationship Owner increase by 2%, Operations Head is no change and rest are increased by 0.5%
Medium = Review Analyst & Relationship Owner increase by 5%, Operations Head is no change and rest are increased by 1%
Larger = Review Analyst & Relationship Owner increase by 15%, Operations Head is no change and rest are increased by 2%.
If anyone could help me with how this If statement would look in the variable it would be much appreciated as I have had no joy so far.
it would be easy if you create 8 different variable for each Occupation Type:
vSubjectMatterExpert
vMILead
vOperationsHead
vPopulationLead
vQualityAssurance
vRelationshipOwner
vReviewAnalyst
vTeamLead
and use if condition to assign respective value to each variable
Hi Sushil,
Thanks for the response, how would you set this IF statement up in say vReviewAnalyst?
Would it be
=if(Occupation='Review Analyst',if([Change Regulation]='Small',0.02,if([Change Regulation]='Medium',0.05,if([Change Regulation]='Large',0.15,0))))
Would that work? Sorry not overly great with these.
Just tried that with no success.
Hi,
Might be easier to keep a matrix in excel and map this to your occupation data
i.e subset sample of how the data will look.
Occupation | ChangeType | ChangePerc |
AML / KYC Subject Matter Expert | No change | 1 |
AML / KYC Subject Matter Expert | Small | 0,1 |
AML / KYC Subject Matter Expert | Medium | 0,2 |
AML / KYC Subject Matter Expert | large | 0,3 |
MI Lead | No change | 1 |
MI Lead | Small | 0,1 |
MI Lead | Medium | 0,2 |
MI Lead | large | 0,3 |
The your slider acts like a selector for the ChangeType Column above.
Then in your expression sum(YourValue) * ChangePerc
Best to have that left joined to your fact table which should have the main measure you use in your expression for performance reasons.
Hope this helps!
Hi,
Thanks for the reply, I am not great with mapping (not great with much it seems). I would be more comfortable with if statements in variables although I cannot even get those to work!
Regards
David
The approach above should be easier, cleaner and better performance.
If you create the matrix as advised above, load the excel file by map i mean you could simply load it in as normal with Occupation field from the above file associated/linked with the occupation field in you fact table.
You can then just use the the expression above.
If you absolutely must use if statements, use this tool:
qlikblog.at - Nested If Generator
Its a massive help for complex if statements.
Just make sure you test each condition seperately to see if they produce correct results and the tool above will help structure it in a easy to understand way.
Hi,
Going to try and give this a go, I have loaded the file in with the 3 fields above. My expression looks like this currently:
+ sum({<Year={'2018'}, Attrition=, Retirement=>}[Demand C])*($(vNewClients) - $(vAutomation) + $(vRisk) + vRegulation )
With vRegulation being the current Change Regulation Piece. Would I just add (vRegulation * ChangePerc) to that and it should work?
Logically, Yes it should work if its setup as explained...
Use your usual expression if it produces the desired result and * by ChangePerc
(YourExp) * Change Perc