5 Replies Latest reply: Oct 26, 2016 10:15 AM by Brianna Knoppow RSS

    Using Distinct in expression for bar chart

    Brianna Knoppow

      Hello, I am having difficulty editing an expression for creating a bar chart.

       

      I have Regions and each Region has multiple Place_ID. Each Place_ID has a distinct Amount. However, Place_ID 2 will always have the same Amount even if it shows up twice. It will always be 400 (in my example below). The goal is that I need a bar (in the bar chart) for each Region that shows the total 'Amount' for that Region. The problem is that when Qlik sees Place_ID2 twice it adds 400 + 400 to make 800. That is incorrect. I need it to use Place_ID2 only once in the calculation so the 400 will only show up once. I have an example below.

       

      Dimension: Region

      Expression: Sum([Amount],DISTINCT[Place_ID])  This is very wrong - but what is correct?

       

       

      For Region 1

      Place_ID | Amount

      1             100

      2             400

      1             100

      3             7000

      2             400

      4              65

      5             900

       

      Place_ID | Amount  (4 was filtered out)   What I would like to see!

      1             100

      2             400

      3            7000

      5             900

       

      *******************************

      For Region 2  

      Place_ID  | Amount

      6            400

      7            900

      7             900

      8             45

      9             400

      10              55

       

      Place_ID | Amount                     What I would like to see!   

      6           400

      7             900

      8            45

      9           400

      10             55

       

      Should show up in bar chart with Regions on x axis, sum on y axis

      Region 1 = sum is 8,400

      Region 2 = sum is 1,800

       

      Thank you