2 Replies Latest reply: May 2, 2013 2:40 AM by Kalyana Sundaram RSS

    Getting Fifteen months average of Sales depends upon Selection

    Kalyana Sundaram

      Hi all,

       

      I have a issue in taking the fifteen months average of sales as per the below conditions.

      Getting average of the sales as the following method.

      If selection is Current year - 3rd month then take average of 12 months in last year + upto Cur. Year 3rd month = Total 15 months sales average

       

      Script

      MaxYear:

      load max(FinCreateYear) as MaxFinYr

      Resident TAGMASTER;

       

       

      Let vMaxFinYr= Peek('MaxFinYr',0,MaxYear);

       

       

      Sales:

      LOAD FIN_MONTH,

           FIN_Year,

           LineAmount,

           month,

           MonthNo,

           StoneRawItemCts,

           StoneRawValue,

           TagItemKey,

           TAGPCS,

           TagRateItemPcs,

           TagRawItemQty,

           TagRawQty,

           year,

           Yearmonth

      FROM

      $(QVDPath)Sales.csv

      (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

      where FIN_Year>($(vMaxFinYr)-1);

       

      Expression:

      sum(TAGPCS)/count(distinct YearMonth)

       

       

      but the value gets only for one year i.e. only 12 months.

       

      Please help me to solve the above problem.

       

      Thanks in Advance,

      Kalyan.D

        • Re: Getting Fifteen months average of Sales depends upon Selection
          jagan mohan rao appala

          Hi Kalyan,

           

          Arrive a date field in your data model like this

           

          Sales:

          LOAD FIN_MONTH,

               FIN_Year,

               LineAmount,

               month,

               MonthNo,

               StoneRawItemCts,

               StoneRawValue,

               TagItemKey,

               TAGPCS,

               TagRateItemPcs,

               TagRawItemQty,

               TagRawQty,

               year,

               Yearmonth,

              MakeDate(year, MonthNo) AS Date

          FROM

          $(QVDPath)Sales.csv

          (txt, codepage is 1252, embedded labels, delimiter is ',', msq)

          where FIN_Year>($(vMaxFinYr)-1);

           

          Now in expression use this

           

          =Sum({<Year=, MonthNo=, month=, YearMonth=,Date={'>=$(=MonthStart(Max(Date), -14))<=$(=Max(Date))'}>}TAGPCS)/count({<Year=, MonthNo=, month=, YearMonth=,Date={'>=$(=MonthStart(Max(Date), -14))<=$(=Max(Date))'}>} distinct YearMonth)

           

          Hope this helps you.

           

          Regards,

          Jagan.