Expression

[smilingjohn]

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

[Anil_Babu_Samineni]

If, Max(Month) returns 12 then should be this?

Sum({<Month = {">=$(=Max(Month)-9) <=$(=Max(Month))"}>} Tickets)

[smilingjohn]

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

[its_anandrjs]

Do you have date field in data model if so i suggest

Sum ({<Datefield={">=$(=date(addmonths(Datefield,-9))) <=$(=max(date(addmonths(Month,0))))"}>} Tickets)

[Anil_Babu_Samineni]

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?

[smilingjohn]

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

[Anil_Babu_Samineni]

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)

[smilingjohn]

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

[luismadriz]

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

[Anil_Babu_Samineni · Accepted solution]

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.