Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Please find the attached qvw file
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
I could not take the max date, since my date field will have maximum date upto 2022 years.
I need every month sales from this month to last 12 months. not the aggregated sum of all the last 12 months sales.
Hi,
Add MonthName as your dimension.
It will work.
Regards
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
I dont want to take monthname as dimension. I need to control it all in the expressions.
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