8 Replies Latest reply: Nov 20, 2015 8:34 AM by Liv ma RSS

    Help creating a line chart with shares as measure - Qlik Sense

    Liv ma

      Hello again

       

      I have the following data structure

       

      field 1 = date (several dates)

      field 2 = customer type (several customer types for each date)

      field 3 = customer id (many per customer type)

      field 4 = transaction id (many per customer)

      field 5 = Value (several per transaction id)

       

      I want to create a line chart (or another visualization) that has:

      - as dimensions: date (on X axis) and customer type (each customer type shown in the chart as a line)

      - as measure: the share of [sum(Value) (per date and customer type)] in [sum(Value) per date]

       

      essentially I am trying to show a pie chart with customer type shares for each date, but for all the dates at once, using one line for each customer type

      the sum of shares (percentages) of customer type for each date should obviously be 100%

       

      or, in other words, I want to disregard the customer type dimension when calculating the denominator, but not the date dimension

       

      I have tried

      1. sum(aggr(sum(value),date,customer_type) / sum(aggr(sum(value),date)

      but it does not show meaningful data

       

      2. sum(aggr(sum(value),date,customer_type) / sum(TOTAL aggr(sum(value),date)

      it shows meaningfull data, but it divides by the total sum of Value in the database, so does not take into account the date


      3. sum(aggr(sum(value),date,customer_type)/sum(aggr(sum(TOTAL value),date)

      but it does not show meaningful data


      your help is highly appreciated

      thank you

      LM