Getting Fifteen months average of Sales depends upon Selection

[kalyandg]

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 - 3^rd month then take average of 12 months in last year + upto Cur. Year 3^rd 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

[jagan · Accepted solution]

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.

[kalyandg]

hi jagan mohan,

thanks for your reply.

the epression logic does not works.

i put the expression in the text object, but no values shown.

but i have tried using the below expression

=Sum({<SaleDate={'>=$(=YearStart(Max(SaleDate), -1))<=$(=Max(SaleDate))'}>}TAGPCS)/RangeSum(13 + Num(Month( Max(SaleDate))))

it works perfectly and values shown are matching.

Thanks,

Kalyan.D