2 Replies Latest reply: Mar 30, 2017 11:24 AM by omar bensalem RSS

    How to get rid of unwated data from Finter pane in Qlik Sense?

    Hari Krishna Rao Bezavada

      Hello Experts,

       

      I am new to Qlik sense. I am in learning process. Here I have a problem with filter pane.

      In my model, I have used 3 dimensions and 1 fact. Below are table names

       

      1. Dim COA

      2. Dim Account

      3. Dim Calendar (Tran Date Calendar, Post Date Calendar)

      4. Fact Transactions

       

      In above model I used Dim Calendar two times. One for Tran date calendar table and one for Post Date calendar. I used this model for create a qlik sense report.

       

      For example if an Account doesn't have any transactions in Fact table, I still can able to see that account number in report with 0 as measure value. I can able to get rid of this kind of data from report by uncheck option in 'Data Handling' in 'Add-on' properties. When I come to filter pane I don't have 'Add-on' properties for Filter pane. Please help me how to handle this kind of situation.

       

      Thanks in advance

       

      --Hari

        • Re: How to get rid of unwated data from Finter pane in Qlik Sense?
          omar bensalem

          Can you share an image of your problem? What do you want to eliminate?
          Thanks !

            • Re: How to get rid of unwated data from Finter pane in Qlik Sense?
              omar bensalem

              Till then, If I fully understand you.

               

              I have sthing similar to that;

              I have a field called : Year

              a Measure : Sum("Car Sales")

               

              Suppose I choose a bar chart:

              Dimension: Year

              Measure : Sum("Car Sales")

               

              The result would be:

              Capture.PNG

               

              Since there is no car sales before 2005; I want to eliminate those years from the chart.

              We go to complement and uncheck the null values and the work is done :

              Capture.PNG

               

              Now, in the other hand, and even so there is no Car Sales before 2005, when we Add the Year field as a filter pane, we will see all the Years:

              Capture.PNG

               

              The question is How to eliminate these unwanted Years?

               

              Let's ask it a different way; What are the Years we want to eliminate?

              => The Years that have no Car Sales in them:

               

              In other work, If Sum of car sales by year =0 , then replace the Year field by Null, else, if there is sales: keep the Year value:

              The expression would be as follow:

              =if(aggr(Sum({1}[Car sales]),Year)=0,Null(),Year)


              Let's create it a master dimension and use it as a filter:

              Capture.PNG



              Capture.PNG


               

               

               

               

              Hope this answers your question!

              Omar,