Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have d date field having values like Jan-16,Feb-16,Mar-16......................Sep17.
I want to display last 12 months of data in X-axis.
Ex: Current month is Feb 17, i have to show on x-axis from Jan-16 to Jan-17 only.
If Current month is Mar-17, i have to show data on x-axis from Feb-16 to Feb-17.
Can any one help on this requirement.
Thanks,
Ravi
This?
num(sum({<Surface={'Total Room'}, CalendarDate = {"$(='>=' & Date(AddYears(Max(CalendarDate), -1), 'MMM-YY') & '<=' & Date(Max(CalendarDate), 'MMM-YY'))"}>}Pass)/(sum({<Surface={'Total Room'}>}Fail)+ sum({<Surface={'Total Room'}>}Pass)+sum({<Surface={'Total Room'}>}Caution)),'#,##0.0%')
First create MonthSerial like this.
Year * 12 + Month
Here is how to do it in script
MonthMap:
Mapping LOAD * INLINE [
Name,Num
Jan,1
Feb,2
.
.
Dec,12
];
Assuming field name of d date is d_date
LOAD *,
ApplyMap('MonthMap',left(d_date,3)) + (right(d_date,2)*12) as MonthSerial
Then in expression of Measure use set analysis like this
Sum( {<MonthSerial={">=$(=max(MonthSerial)-12) <$(=max(MonthSerial))"}>} Sales)
Hi ,
Is there any way to restrict by using calculated dimension in the chart.
You cannot use calculated dimension in Set Analysis and Set analysis is the only way you restrict data,
I'll need a sample app to do that without scripting.
This?
num(sum({<Surface={'Total Room'}, CalendarDate = {"$(='>=' & Date(AddYears(Max(CalendarDate), -1), 'MMM-YY') & '<=' & Date(Max(CalendarDate), 'MMM-YY'))"}>}Pass)/(sum({<Surface={'Total Room'}>}Fail)+ sum({<Surface={'Total Room'}>}Pass)+sum({<Surface={'Total Room'}>}Caution)),'#,##0.0%')
Thanks Sunny,
Its working.
Hi Sunny,
This expression is working for last 12 months.
I am having calendar dates from Jan-16 to Sep-17,so it is showing data for Oct-16 to Sep 17.
My requirement is ,it suppose to show Feb-16 to Jan-17.
If Calendar date is Mar-17 ,then my o/p is Mar-16 to Feb-17.
Can you help on this requirement.
Thanks,
Ravi
Calendar date is Jan-16 to Sep-17 and you want to see from Feb-16 to Jan-17? What is the logic here? Can you explain it?
My Chart always should show the data of last 12 Months.
Ex:Current month is Feb 17, so my chart should display data from Feb-16 to Jan-17.
If i reload the report in March-17, the chart must have data from Mar-16 to Feb-17.
If i reload the report in Apr-17,the chart must have data from Apr-16 to Mar-17.The highlighted month always be [Current month-1 ] and total months in the graph are 12.