7 Replies Latest reply: Nov 10, 2011 5:19 AM by Sam Vile RSS
      • I'm fairly sure this can be done....but...

        Hello Sam,

         

        For list box with conditions, create a list box with expression with simple ifs:

        if(FUM <250,000 and MEMBERS<10 ,FundNames)

         

        Regards,

        Kiran.

          • I'm fairly sure this can be done....but...

            Hi,

             

            I don't actually need a list box based on the criteria...I need the app itself to change based on the users selection.

             

            e.g. A table box showing 50 Fund Names and the FUM...when a user selects <250,000 the Fund Names that have FUM of <250,000 are left and the others disappear.

             

            Does that make sense?!

              • I'm fairly sure this can be done....but...

                Qlikviews native filters only apply for equal to condition. For this you need to create calculated dimensions like,

                 

                IF(FUM<250000,'<250000','>=250000'). Having this in a list box, if the first one is selected automatically the filter is applied on your data model.

                 

                Requirement is genuine but usually this is how I do. If you need the filter to apply for specific charts, you can use set analysis.

                 

                Regards,

                Kiran.

                  • Re: I'm fairly sure this can be done....but...
                    John Witherspoon

                    I assume there is some overlap between these conditions.  Basically, you have FOUR different cases:

                     

                    1) <250,000 FUM & < 10 MEMBERS
                    2) >=250,000 FUM & < 10 MEMBERS
                    3) <250,000 FUM & >= 10 MEMBERS
                    4) >=250,000 FUM & >= 10 MEMBERS

                     

                    And I believe you want this behavior:

                     

                    If "<250,000 FUM" is selected, you want cases 1 and 3.
                    If "<10 MEMBERS" is selected, you want cases 1 and 2.
                    If both are selected, you want cases 1, 2 and 3.
                    If neither is selected, you want cases 1, 2, 3 and 4.

                     

                    Is that right?  If so, see the attached example.  Here's the script:

                     

                    Data:
                    LOAD recno() as Case, * INLINE [
                    FUM, MEMBERS
                    200000, 5
                    300000, 5
                    200000, 15
                    300000, 15
                    ];

                     

                    NewTable:              LOAD DISTINCT Case,'<250,000 FUM' as NewField RESIDENT Data WHERE FUM <250000;
                    CONCATENATE (NewTable) LOAD DISTINCT Case,'<10 MEMBERS'  as NewField RESIDENT Data WHERE MEMBERS <10;

                      • I'm fairly sure this can be done....but...

                        Hi John,

                         

                        You're right with your line of thinking...can you help me with the next steps please?

                         

                        In my QVW I have the following 2 tables:

                         

                        [FUM by Fund]:
                        LOAD [Fund Name],
                             [Fund No],
                             [Fund Value],
                             Year
                        FROM
                        [Y:\Work\Corporate\Project Work\Qlikview\FUM By Fund\FundFUM.qvd]
                        (qvd);

                         

                        [Members by Fund]:
                        LOAD MemberExtractDate,
                             MemberFundName,
                             MemberFundName & '-' & MemberExtractDate as Key,
                             MemberFundNo,
                             TotalMembers,
                             MemberPlatform
                        FROM
                        [Y:\Work\Corporate\Project Work\Qlikview\FUM By Fund\FundMembers.qvd]
                        (qvd);

                         

                        I think what needs to happen is to apply your theory but with these two tables instead of the inline table. The FUM field should link to the Fund Value and the MEMBERS to the TotalMembers.

                         

                        Is that possible?

                          • Re: I'm fairly sure this can be done....but...
                            John Witherspoon

                            These two tables appear to have no relationship to each other.  Are they supposed to link by fund number?  If so, probably do something like this.

                             

                            [FUM by Fund]:
                            LOAD [Fund Name],
                                 [Fund No],
                                 [Fund Value],
                                 Year
                            FROM
                            [Y:\Work\Corporate\Project Work\Qlikview\FUM By Fund\FundFUM.qvd]
                            (qvd);


                            [Members by Fund]:
                            LOAD MemberExtractDate,
                                 MemberFundName,
                                 MemberFundName & '-' & MemberExtractDate as Key,
                                 MemberFundNo as [Fund No],
                                 TotalMembers,
                                 MemberPlatform
                            FROM
                            [Y:\Work\Corporate\Project Work\Qlikview\FUM By Fund\FundMembers.qvd]
                            (qvd);

                             

                            NewTable:             
                            LOAD DISTINCT [Fund No],'<250,000 FUM' as NewField
                            RESIDENT [FUM by Fund] WHERE [Fund Value] <250000;
                            CONCATENATE (NewTable)
                            LOAD DISTINCT [Fund No],'<10 MEMBERS' as NewField
                            RESIDENT [Members by Fund] WHERE TotalMembers <10;