Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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.
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)
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)
Hi Ruben,
Thanks for suggestion, i need in Dimension wise these date fields.
how do i achieve this?
can you suggest me?
I can but as both Sunny's provided a working solution you can choose one of those more detailed answers.
Hahahaha
Hi Sunny,
Thanks for suggestion
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