Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All
I have bar chart which should display always 9 months from current date.
this is December month therefore it should display from dec to April.(4 to 12)
The month is in number format it displays for Jan- 1, Feb-2....and so on ... as shown in the the screenshot.
Iam using the the below expression expression to display 9 month data ..
Please correct me ..this is not working .
The dimension is Month
expression :
Sum ({<Month={">=$(=date(addmonths(Month,-9),'MMM-YYYY'))<=$(=max(date(addmonths(Month,0)),'MMM-YYYY'))"}>}Tickets)
Thanks in advance
Not entirely sure, What are you going here. Even thought i have like
Sum({<DateField = {">=$(=Date(AddMonths(Max(DateField),-9),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)
Some cases, This may works or won't work if we have missed date fields with at least 0 values. So, I would always generate Calendar script to date field for all dates. And, Finally this should work if MonthStart Date like
Sum({<DateField = {">=$(=Date(MonthStart(AddMonths(Max(DateField),-9)),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)
Note - If applicable, Please mark it as helpful and correct answer to close this thread.
If, Max(Month) returns 12 then should be this?
Sum({<Month = {">=$(=Max(Month)-9) <=$(=Max(Month))"}>} Tickets)
Hi Anil
Thanks for the reply ..
Next year it will show jan ..
So will this still diplay 9 month from Jan-2018 back to May-2017?
totally nine months from current date of Month...
Thanks
Do you have date field in data model if so i suggest
Sum ({<Datefield={">=$(=date(addmonths(Datefield,-9))) <=$(=max(date(addmonths(Month,0))))"}>} Tickets)
100% agree, But in your image it is showing as numbers for month. But, In your statement using MMM-YYYY format for Month to degrade. What does you mean that?
Hi Anil...
Your Expression works for 9 months ...and the data is accurate..
So i am going ahead with this..
My only question will it always show nine months even if it eneters the next year jan or Feb ?
Thanks
Yes, It is? But the concept we need to understand the concept of Forecast data. that is the case you need to bifurcate till today only not in forecast data like
Sum({<Month = {">=$(=Max(Month)-9) <=$(=Num(Month(Today())))"}>} Tickets)
Thanks Anil
got it.
Just i have one more query .
In one of my other chart the date format is like 1/1/2017 as shown in the screenshot.
and here also i need to show till nine months ...
with your logic i tried to use expression like below
Sum({<Month = {">=$(=Date(Max(Month))-9) <=$(=Date(Max(Month)))"}>} Tickets)
but this doenst give me the proper result ..
do i need to make any specific change here ?
Thanks
Hi,
If you're worried about year then you need to make sure your definition of Month has that concept.
You could use something like this to define Month:
Dual(Year(DateField) & '-' & Month(DateField), MonthStart(DateField)) as YearMonth or something prettier...
Once the field has been dualed with a number that recognises the year and month, then you can think of substracting a number of Months as you've been advised in the other posts
I hope this helps,
Cheers,
Luis
Not entirely sure, What are you going here. Even thought i have like
Sum({<DateField = {">=$(=Date(AddMonths(Max(DateField),-9),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)
Some cases, This may works or won't work if we have missed date fields with at least 0 values. So, I would always generate Calendar script to date field for all dates. And, Finally this should work if MonthStart Date like
Sum({<DateField = {">=$(=Date(MonthStart(AddMonths(Max(DateField),-9)),'Your Date Format Here')) <=$(=Date(Max(DateField), 'Your Date Format Here'))"}>} Tickets)
Note - If applicable, Please mark it as helpful and correct answer to close this thread.