Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Everyone,
I have an issue with trying to display line chart for 12 month rolling. Basically i need to display last 12 month of sales .
For current month i need current month -1 ( we need last month as the Max) and 12 month back.
1. I'm using line chart
2. Measure is SalesDollars
3. I have fields such as: Year_MonthText ( example: 2014-Jan ) , Year_Month ( example: 2014-01) , OrderYear , OrderMonth.
Please help i tried searching for solution and tried every solution out there for some reason graph display one dot and with no sales. It just doesn't work.
.
Here is what i tried.
SUM({$<Year_MonthText = {">=$(=(Max(Year_MonthText)-11))<=$(=Max(Year_MonthText)-1)"} =>}SalesDollars) ---did not work
Sum({$<MonthYear = {“>=$(=Max(MonthYear) – 11)<=$(=Max(MonthYear)-1)”},OrderYear= ,OrderMonth = >} SalesDollars) ---did not work
---------------------
But if i use year field WHICH I DO NOT NEED for this project it Works
Sum({<OrderYear= {'<=$(=Max(OrderYear)) >=$(=Max(OrderYear)-1) '}>} SalesDollars) -- Works but i dont need to use year i need 12 month calculation .
Please help !!! Thank you so much in advance
Sum({<SeqMonth= {">=$(=Max(SeqMonth)- 11) < $(=Max(SeqMonth))"}>} SalesDollars)
Hi,
you can subtract a number to a string (ex: Jan-2015 - 11 <---- This doesn't not exist).
For YearMonth, if this is in the format of 201507 (July 2015) this won't work either: 201507 - 11 = 201496 <- this date doesn't exist.
Instead, create a sequential month number in the script like the following:
Load *, 12 * YEARFIELD + MONTHFIELD as SeqMonth;
YearField and MonthField should be numbers.
This will create a sequential and unique number for each month of the year. You can the do this:
Sum({<SeqMonth= {">=$(=Max(SeqMonth)) - 11)"}>} SalesDollars)
JV
Hi ,
Thank you for the reply. I tried to do that and it still didnt work i got one dot on chart with no value (0 sales) .
This is what i got in my SeqMonth field after doing 12 * YEARFIELD + MONTHFIELD as SeqMonth
I used following script for measure in chart Sum({<SeqMonth= {">=$(=Max(SeqMonth)) - 11)"}>} SalesDollars)
Try changing your expression to this: Sum({<SeqMonth= {">=$(=Max(SeqMonth)- 11)"}>} SalesDollars)
This is Great!!! It worked perfectly!
Is it possible with this approach to exclude current month? I want it to be previous month and 12 months back. Thank you!
Sum({<SeqMonth= {">=$(=Max(SeqMonth)- 11) < $(=Max(SeqMonth))"}>} SalesDollars)
Thank you !!! I really appreciate your help.