Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I need to take current and previous month sales based on the selected month and year
My Sales Month looks like this 1,2,3,4,5,6,7,8,9,10,11,12
My Sales Yerar looks like this 2011,2012,2013
To calculate current month sales i used this expression below (based on selected month),
=
sum({<SALES_MONTH={"$(=max(SALES_MONTH))"}>} $(vSales)It works but when i try to calculate the previous month using the expression below, value is coming as "0"
=
sum({<SALES_MONTH={"$(=max(SALES_MONTH)-1)"}>} $(vSales)Kindly guide where i'm wrong in this expression
Thanks,
Selva
Create a numeric month field in the script: num(month(date#(MonthName,'MMM'))) as MonthNumber
You'll also need an incremental month field because the monthnum of Jan is 1 and the previous month is not 0, but 12 in the previous year. You can add a field like this: Year*12+num(month(date#(MonthName,'MMM'))) as PeriodNumber. You can then use the PeriodNumber field in the set analysis expressions:
Current: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber))>} Sales)
Previous Month: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber)-1)>} Sales)
Same Month previous year: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber)-12)>} Sales)
for current--
sum({<SalesMonth={"$(=(SalesMonth))"},SalesYear={"$(=(SalesYear))"}>}Sales)
for previous--
sum({<SalesMonth={"$(=(SalesMonth)-1)"},SalesYear={"$(=(SalesYear))"}>}Sales)
PFA. Hope it helps.
Regards,
Som
Hi Som,
Suppose my Sales month look like this ( Jan,Feb,Mar,Apr etc to Dec)
How to calculate the previous year.
Thanks,
Selva
Hi Selva,
Your expresion look fine. Can you tell me the definition of $(vSales)?
Regards,
Sokkorn
Create a numeric month field in the script: num(month(date#(MonthName,'MMM'))) as MonthNumber
You'll also need an incremental month field because the monthnum of Jan is 1 and the previous month is not 0, but 12 in the previous year. You can add a field like this: Year*12+num(month(date#(MonthName,'MMM'))) as PeriodNumber. You can then use the PeriodNumber field in the set analysis expressions:
Current: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber))>} Sales)
Previous Month: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber)-1)>} Sales)
Same Month previous year: sum({<Sales_Year=,Sales_Month=,PeriodNumber={$(=max(PeriodNumber)-12)>} Sales)
Thanks Gysbert
Dear Selva,
In that case, still you can make a DATE using your table fields.
Like Date(Date#('2012-Jan-01','YYYY-MMM-DD'),'DD/MM/YYYY')
Regards,
Som