5 Replies Latest reply: Oct 31, 2017 8:27 AM by Simon Minifie RSS

    how to select limited list into a chart qlik sense

    Tal Sibony

      Hi, 

       

      I've loaded a data set that contains 4 columns:

      • Date (since the beginning of the month)
      • Account
        • sales
        • costs

       

      Then, I would like to create a bar chart with the top 10 accounts by the proportion: sales/costs (total sum of all history up to week ago from now)

      But, I want to consider only accounts with over then $100 sales in total on the exact date of week ago

       

      When I try to apply this in the dimension settings:

      if(Date=today()-8 and sum(sales)>100,account)

      I’m getting an error (‘invalid dimension’)

       

      When I try to apply this in the measure settings:

      if(

          sum(

                   if(

                       Date=Today()-8,Sales

                       )

                    )>100, sum(

                                if(

                                   Date<Today()-7,sales

                                   )

                                )

      /

      sum(

              if(

                 Date<Today()-7,sales

                 )

              )

      )

       

      I get weird account list with zeros values for the proportion (seems like for part of the account list the measure is not calculated due to the if condition)

       

       

      A little bit long example but hope you will find a place in your heart to help

       

      Tnx!

      Tal