Variable If Statement Help

[#1]

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.

[sushil353]

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

[#3]

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.

[#4]

Just tried that with no success.

[Anonymous]

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!

[#6]

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

[Anonymous]

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.

[Anonymous]

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.

[#9]

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?

[Anonymous]

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