Last 12 Months Sales

[manideep78]

Dear Community,

I have a date column which is in number format. I want to display the sales of last 12 months including this month. April-15, Mar-15,.., May-14.

My dimension would be say 'Item'.

Note: I want that date expression (which filters the particular month like april-15, mar-15,.....) to be in a variable so that, I can easily reduce one month by subtracting any value from that variable in the set expression. so that I need not write the date expression again and again in all the 12 months sales expressions.

Please suggest any better way of doing it.

Thanks in advance

Regards,

Manideep

[#2]

hi,

use this expression. put your date as place of TRANDATE(numeric field is fine)

vMaxDate1=Date(Max(TRANDATE))

vRollingDate1=AddMonths(Max([TRANDATE]),-11)

(sum({$<FinYear= ,FinMonth=,TRANDATE={"<=$(vMaxDate1) >=$(vRollingDate1)"}>}  Amount))

Regards

vimlesh

[#3]

Please find the attached qvw file

[PrashantSangle]

Hi,

Create MonthNAme field in Script like

MonthName(datefield) as MonthName,

then in expression write below set analysis

sum({<dateField={">=$(=date(addmonths(today(),-11)))<=$(=date(today()))"}>}Sales)

or write

max(date) instead of today() if you want on selection basis.

Regards

[manideep78]

I could not take the max date, since my date field will have maximum date upto 2022 years.

[manideep78]

I need every month sales from this month to last 12 months. not the aggregated sum of all the last 12 months sales.

[PrashantSangle]

Hi,

Add MonthName as your dimension.

It will work.

Regards

[manideep78]

My dimension is Item  and I need sales for every month starting from this month to last 12 months and not aggregated sum for all the last 12 months

[manideep78]

I dont want to take monthname as dimension. I need to control it all in the expressions.

[#10]

hi,

vMaxDate1=Date(Max(TRANDATE))

vRollingDate1=AddMonths(Max([TRANDATE]),-11)

if you will select 2022 then it will give result 2022 to 2021 .because you are making variable with your date.

don't use today for maxdate.

this will work what you want.

or send a sample file

Regards

Vimlesh