Date Calculation

[khasimvali85]

Hi Community,

I have date field and sales data, i have to create bar chart and the dimension is date field, sales are using sum(sales).

my requirement is the date field as dimension like to create current month, previous month, last year current month.

i need only use calculated dimensions using Date Field as per data

1, Current Month and Current Year (June-2015)

2, Previous Month and Current Year(May-2015)

3, Last Year Current Month(June-2014)

how do I achieve this?

Ex: Date field is Date

       Expression in SUM(Sales)

The chart like attachment

[rubenmarin]

Hi, you can create 3 expressions, without dimensions, using set analisys to select the periods for each expression.

In the label of each expression you can create the expression that returns the month-year of each bar.

[sunny_talwar]

Try this:

Dimension: =MonthName(DATE)

Expression:

=Sum({<DATE = {"$(='>=' & Date(MonthStart(Max(DATE)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(Max(DATE)), 'DD-MM-YYYY'))"} +

  {"$(='>=' & Date(MonthStart(AddMonths(Max(DATE), -1)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(AddMonths(Max(DATE), -1)), 'DD-MM-YYYY'))"} +

  {"$(='>=' & Date(MonthStart(AddYears(Max(DATE), -1)), 'DD-MM-YYYY') & '<=' & Date(MonthEnd(AddYears(Max(DATE), -1)), 'DD-MM-YYYY'))"}>}SALES)

[sasikanth · Accepted solution]

HI

Instead you can write all expressions ,

please find the attachment

Exp 1) =sum({<New_Date= {$(=date(max(New_Date),'YYYYMM'))}>}SALES)

Exp 2) =sum({<New_Date= {$(=date(addmonths(max(New_Date),-1),'YYYYMM'))}>}SALES)

Exp 3)= sum({<New_Date={$(=date(AddYears(max(New_Date),-1),'YYYYMM'))}>}SALES)

[khasimvali85]

Hi Ruben,

Thanks for suggestion, i need in Dimension wise these date fields.

how do i achieve this?

can you suggest me?

[rubenmarin]

I can but as both Sunny's provided a working solution you can choose one of those more detailed answers.

[sunny_talwar]

Hahahaha

[khasimvali85]

Hi Sunny,

Thanks for suggestion 

[sunny_talwar]

No offense Khasimvali85‌, but all three of us provided you with some type of solution/suggestion. How is this a 'Assumed Answer'? Please reward people who are trying to help you. The 'Correct Answer' and 'Helpful Answer' are there for a reason.

Best,

Sunny