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

    Help needed with aggr function in chart - Qlik Sense

    Liv ma

      Hello dear Qlik community,

       

      would very much appreciate your help with the following situation

       

      I have a data structure as per below

      field 1 = date (there are several dates)

      field 2 = customer id (there are several customers as of each date)

      field 3 = transaction id (there are several transactions for each customer as of each date)

      field 4 = Value 1 (1 or more for each transaction, so the transaction id may appear more than once for each customer))

      field 5 = Value 2 (1 or more for each transaction, so the transaction id may appear more than once for each customer)

       

      I need to calculate, for each customer as of each date, the [minimum between sum(Value 1) and sum(Value 2)], i.e.disregarding transactions

      for this, I use this expression

      aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id)

       

      then, I need to calculate, for each reporting date, the sum of these minimum values for all the customers as of each date, so obviously I use

      sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id))


      this works fine


      now what I want to do is create a visualisation (a bar chart) that plots, for each date, the sum of the measure described above


      now here is the problem:

      I create a bar chart using as dimension the date and as the measure the expression above, i.e. sum(aggr(if(sum(Value 1)>sum(Value 2),sum(Value 2),sum(Value 1)),customer id))


      but the displayed values for each date are incorrect: some dates show very large values, some very small values


      what dazzles me is that if I select one single date (using a filter), the calculation is always correct, but if more than one date is selected, the result is unusable


      thank you

      L