4 Replies Latest reply: Mar 22, 2018 5:14 AM by Chanty 4u RSS

    Average field data basing 3 months Timestamp field

    Michael Anywar

      Hello, am trying to calculate the average Qty from the previous 3 months, but it not working.

      Below are the expressions i tried out. I want use as Measures for visualisation.

       

      However my Date field is Timestamp.  Format: DD-MM-YYY hh:mm

       

      I even used variables;

       

      =Sum({<date={'>=1/12/2017 < = 1/3/2018'}>}Qty)

       

      I even tried using created variables:

       

      vLast3MonthStartDate= MonthStart(Today(),-4 )

       

      vLastMonthEnd= MonthEnd(Today(),-1 )

       

      vSumLast3MonthQuantitySold = Sum({$<date={">=$(vLast3MonthStartDate)<$(vLastMonthEndDate)"}>}Qty)

       

      vQuartyAveragequantity = ((Sum $(vSumLast3MonthQuantitySold))/3)