5 Replies Latest reply: May 15, 2018 6:26 PM by Youssef Belloum RSS

    Having an issue aggregating by Year_Month

    Brian Bohr

      Given the following data set I am trying to use Qlik Sense to come up with a formula that can be used in a chart or table that will givem the Number of Discussions for each Month by State based upon the Max Index.

         

      DiscussionsYear_MonthStateIndex
      32018-JanNC1
      32018-JanNC2
      42018-JanNC3
      42018-FebNC4
      42018-FebNC5
      52018-FebNC6
      12018-JanVa1
      12018-JanVa2
      22018-JanVa3
      22018-FebVa4
      32018-FebVa5
      42018-FebVa6
      02018-JanCa1
      12018-JanCa2
      22018-JanCa3
      32018-FebCa4
      42018-FebCa5
      52018-FebCa6

      So when it is all said and done I will see

      NC 2018-Jan 4

      NC 2018-Feb 5

      Va 2018-Jan 2

      Va 2018-Feb 4

      Ca 2018-Jan 2

      Ca 2018-Feb 5

       

      I have tried the following but it does not work:

      IF(Max(Index), Aggr(Sum(Discussions), [Year_Month],State))

       

      This gets me close but it seems to ignore the IF(Max(Index)) portion and just sums up all the discussions. Can anyone point me in the right direction. Thanks

       

      Brian