Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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