1 Reply Latest reply: Oct 1, 2017 5:30 AM by Stefan Wühl RSS

    How to get average total, count per month?

    Minnow Noir

      I am trying to plot the average transaction count and total value per calendar month (e.g., we average 20 transactions, and $100M, in September each year) in a bar chart from a table that has the date and value of each transaction. 

       

      Say I have the following table that has the date and value of each transaction (only showing transactions from last and current September for simplicity):

       

      Date, Value

      9/1/16, 100.00

      9/2/16, 200.00 

      9/1/17, 300.00

      9/2/17, 400.00 

       

      With this data, we average 2 transactions, and $250.00, each September.

       

      I created a master dimension on calendar month ([Txn Month]), which is just Jan, Feb, Mar, etc.

       

      I added a bar chart.

       

      I set the dimension to [Txn Month].  I don't know how to express either measure.  Based on what I found on this forum, I tried using

       

      Avg(Aggr(Sum([Value]), [Txn Month]))

      Avg(Aggr(Count([Value]), [Txn Month]))


      but nothing shows up in the bar chart other than the [Txn Month] axis dimension values. (No bars show up for either measure.)  I tried adding just the Aggr() expressions to tables to see what was in there, but nothing displayed.


      Any ideas?

       

      Thanks

        • Re: How to get average total, count per month?
          Stefan Wühl

          Do you see the total value for the month when just using

           

          =Sum(Value)

           

          ? If not, you need to check your data model and data (e.g. Value needs to be interpreted as number when reading in).

           

          For your averages, try

           

          =Sum(Value) / Count(Value )

          =Count(Value) / Count(DISTINCT Monthstart(Date) )

           

          Instead of Monthstart(Date) (which also only works when Date is interpreted as date showing a dual value with a numeric part), you can use any field that represents a unique Month & Year combination.