11 Replies Latest reply: Sep 28, 2017 11:11 AM by Sunny Talwar RSS

    How to exclude the filter selection on the straight table w.r.t. Dimension

    RANJIT KAKADE

      Hi Qlik Experts,

       

      I have created a Dashboard in QlikSense,By using a straight table,in which displaying the categories and their totals wrt to amount and budget measures,but after selection of filters the total details(Categories) are not displaying,PFB details ,for more information,and kindly provide me the appropriate solution for how to restrict the dimensions after selection of filters:

       

      1. Before filter Selection:

       

      Flex.PNG

      2. After Filter Selection:

       

      Flex1.PNG

       

      In the 2nd screenshot after selection of filters the Totals of Categories are not appearing on the Qliksense dashboard.

      please provide me solution for the same.

       

      Thanks and regards,

       

      Ranjit Kakade.

       

      Message was edited by: RANJIT KAKADE

        • Re: How to exclude the filter selection on the straight table w.r.t. Dimension
          Sunny Talwar

          May be like this

           

          if(WildMatch(Only({1} MagCategoryName),'Sales of Products - TOTAL')>0,

          sum({1<MagCategoryID={'98','428'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Sales of Freight - TOTAL')>0,

          sum({1<MagCategoryID={'99','421'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'GR6122-Sales of Other Services - TOTAL')>0,

          sum({1<MagCategoryID={'435','436'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'FX Hedge impact on Sales - TOTAL')>0,

          sum({1<MagCategoryID={'101','433'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Sales of Production - TOTAL')>0,

          sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Sales-Correction(TOTAL)')>0,

          sum({1<MagCategoryID={'419','438'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Total Sales in COS')>0,

          sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101','433','658','103','104','419','438','659'}>} Total $(vNetBalance)),

           

          if(WildMatch(Only({1} MagCategoryName),'GR622 - Raw Materials - Total')>0,

          sum({1<MagCategoryID={'430','439','689','440','442','690','443','444','660','445','446','691','447','448','692','449'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Crude Ore Used - TOTAL')>0,

          sum({1<MagCategoryID={'430','449'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Purchased Ore Used - TOTAL')>0,

          sum({1<MagCategoryID={'440','442'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),' Mineral reserves depletion and royalties-TOTAL')>0,

          sum({1<MagCategoryID={'110','452'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'GR626-IG Trading Purchase - TOTAL')>0,

          sum({1<MagCategoryID={'519','521'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

          sum({1<MagCategoryID={'599','600'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

          sum({1<MagCategoryID={'117','611'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Overburden&StrippingCosts(TOTAL)')>0,

          sum({1<MagCategoryID={'121','614'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Detail V1(Total)')>0,

          sum({1<MagCategoryID={'124','616'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

          sum({1<MagCategoryID={'125','617'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

          sum({1<MagCategoryID={'142','624'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Gas(TOTAL)')>0,

          sum({1<MagCategoryID={'118','612'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Energy-Others(TOTAL)')>0,

          sum({1<MagCategoryID={'119','613'}>} Total $(vNetBalance)),

          if(WildMatch(Only({1} MagCategoryName),'Energy - Coal(TOTAL)')>0,

          sum({1<MagCategoryID={'184','615'}>} Total $(vNetBalance)),

          sum({1<MagCategoryName={'*'}-{"*Std"}>}$(vNetBalance)))))))))))))))))))))))

            • Re: How to exclude the filter selection on the straight table w.r.t. Dimension
              RANJIT KAKADE

              Hello Sunny,

               

              my requirement is to  not restrict  the data w.r.t. dimension not related to measure i.e.

              If I select any filter after that ,all MagCategory should be display in my dashboard,wherever I select any filter . but

              right now If I select any filter only associated data is reflecting in dashboard.

                • Re: How to exclude the filter selection on the straight table w.r.t. Dimension
                  Sunny Talwar

                  Like I mentioned, I am not sure why this happens... would you be able to share a sample with dummy data may be?

                    • Re: How to exclude the filter selection on the straight table w.r.t. Dimension
                      RANJIT KAKADE

                      Hi Sunny ,

                      I have resolved my issue by using following expression:

                       

                      if(WildMatch(Only({1} MagCategoryName),'Sales of Products - TOTAL')>0,

                      sum({1<MagCategoryID={'98','428'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Sales of Freight - TOTAL')>0,

                      sum({1<MagCategoryID={'99','421'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'GR6122-Sales of Other Services - TOTAL')>0,

                      sum({1<MagCategoryID={'435','436'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'FX Hedge impact on Sales - TOTAL')>0,

                      sum({1<MagCategoryID={'101','433'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Sales of Production - TOTAL')>0,

                      sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Sales-Correction(TOTAL)')>0,

                      sum({1<MagCategoryID={'419','438'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Total Sales in COS')>0,

                      sum({1<MagCategoryID={'98','420','678','99','421','687','435','436','688','101','433','658','103','104','419','438','659'}>} Total $(vNetBalance)),

                       

                      if(WildMatch(Only({1} MagCategoryName),'GR622 - Raw Materials - Total')>0,

                      sum({1<MagCategoryID={'430','439','689','440','442','690','443','444','660','445','446','691','447','448','692','449'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Crude Ore Used - TOTAL')>0,

                      sum({1<MagCategoryID={'430','449'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),' Raw Materials - Purchased Ore Used - TOTAL')>0,

                      sum({1<MagCategoryID={'440','442'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),' Mineral reserves depletion and royalties-TOTAL')>0,

                      sum({1<MagCategoryID={'110','452'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'GR626-IG Trading Purchase - TOTAL')>0,

                      sum({1<MagCategoryID={'519','521'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

                      sum({1<MagCategoryID={'599','600'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),' Freight - Other - TOTAL')>0,

                      sum({1<MagCategoryID={'117','611'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Overburden&StrippingCosts(TOTAL)')>0,

                      sum({1<MagCategoryID={'121','614'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Detail V1(Total)')>0,

                      sum({1<MagCategoryID={'124','616'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

                      sum({1<MagCategoryID={'125','617'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Detail V2(TOTAL)')>0,

                      sum({1<MagCategoryID={'142','624'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Gas(TOTAL)')>0,

                      sum({1<MagCategoryID={'118','612'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Energy-Others(TOTAL)')>0,

                      sum({1<MagCategoryID={'119','613'}>} Total $(vNetBalance)),

                      if(WildMatch(Only({1} MagCategoryName),'Energy - Coal(TOTAL)')>0,

                      sum({1<MagCategoryID={'184','615'}>} Total $(vNetBalance)),

                      sum({1<MagCategoryName={'*'}-{"*Std"},[Business Unit Name]=,CompanyName=>}$(vNetBalance)))))))))))))))))))))))

                • Re: How to exclude the filter selection on the straight table w.r.t. Dimension
                  Sunny Talwar

                  I used a comparison tool to compare the two expressions.... the only thing that was different between the two expression is the last line

                   

                  Mine

                  sum({1<MagCategoryName={'*'}-{"*Std"}>}$(vNetBalance)))))))))))))))))))))))

                   

                  vs. yours

                  sum({1<MagCategoryName={'*'}-{"*Std"},[Business Unit Name]=,CompanyName=>}$(vNetBalance)))))))))))))))))))))))

                   

                  Do you really need to ignore selections in ,[Business Unit Name]=,CompanyName= even though you are using 1 to ignore all selections?