Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have date field which has dates like Jan-14, Feb-14, Mar-14......... Dec-15 and the data keeps increasing like Jan-16, Feb-16 and so on.
I have to show always latest 12 months date in the chart and tables.
How to achieve this condition?
It depends on what your 'date' field really contains. You need a date field with real dates or numbers to be able to calculated a maximum value to retrieve the latest value. Once you have that you can calculate what the latest 12 months are.
In this load statement a date field is created from a field that contains text values like 'Jan-14'.
LOAD
... some fields...,
date(date#(MyDate,'MMM-YY'),'MMM-YY') as MyDate,
....some other fields...
FROM ...
Now that MyDate is a real date you can use it in expressions to make the chart show the latest 12 months. Suppose you have an expression in your chart like sum(Sales). To make sure the charts shows only the information of the latest 12 months you add a records set definition to it:
sum({<MyDate={">$(=AddMonths(Max(MyDate),-12))"}>}Sales).
Max(MyDate) calculates the latest month. AddMonths(...,-12) then calculates the MyDate value that's 12 months earlier. And the larger then operator {"> ..."} is used to filter the field MyDate to select only those MyDate values that are larger than the value that's 12 months before the maximum, i.e. latest, MyDate value.
try this
Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=$(=MonthStart(Today(), -11))<=$(=Today())’}>} Sales )
for more check this link
see these threads:
How to display Last 12 Month in Dimension in Bar Chart
Displaying last 12 months trend
Make sure format should be same for everyone.
It depends on what your 'date' field really contains. You need a date field with real dates or numbers to be able to calculated a maximum value to retrieve the latest value. Once you have that you can calculate what the latest 12 months are.
In this load statement a date field is created from a field that contains text values like 'Jan-14'.
LOAD
... some fields...,
date(date#(MyDate,'MMM-YY'),'MMM-YY') as MyDate,
....some other fields...
FROM ...
Now that MyDate is a real date you can use it in expressions to make the chart show the latest 12 months. Suppose you have an expression in your chart like sum(Sales). To make sure the charts shows only the information of the latest 12 months you add a records set definition to it:
sum({<MyDate={">$(=AddMonths(Max(MyDate),-12))"}>}Sales).
Max(MyDate) calculates the latest month. AddMonths(...,-12) then calculates the MyDate value that's 12 months earlier. And the larger then operator {"> ..."} is used to filter the field MyDate to select only those MyDate values that are larger than the value that's 12 months before the maximum, i.e. latest, MyDate value.