9 Replies Latest reply: Nov 17, 2016 5:04 AM by Deran Naidoo RSS

    Variable If Statement Help

    David Lyness

      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.

        • Re: Variable If Statement Help
          Sushil Kumar

          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

          • Re: Variable If Statement Help
            Deran Naidoo

            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.

             

              

            OccupationChangeTypeChangePerc
            AML / KYC Subject Matter ExpertNo change1
            AML / KYC Subject Matter ExpertSmall0,1
            AML / KYC Subject Matter ExpertMedium0,2
            AML / KYC Subject Matter Expertlarge0,3
            MI LeadNo change1
            MI LeadSmall0,1
            MI LeadMedium0,2
            MI Leadlarge

            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!

              • Re: Variable If Statement Help
                David Lyness

                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

                  • Re: Variable If Statement Help
                    Deran Naidoo

                    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.

                  • Re: Variable If Statement Help
                    David Lyness

                    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?

                  • Re: Variable If Statement Help
                    Deran Naidoo

                    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.