Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I need to create a bar chart for every 6 months sales. I have a date field. How can I create a dimension (extracting from date field) for every 6 months interval on X-axis ?
For ex: Jan-2008, Jul-2008,Jan-2009, Jul-2009 so on…
Many thanks
I'd add another field into the Calendar table and calculate an extra field that can be used as a dimension.
I'm sure there is more elegant way of calculating it, but at the moment all i can think of id this:
date(if(month(Date) <7, YearStart(Date), YearStart(Date, 0, 7) ), 'MMM-YYYY') as SixMonthsBucket
You can use the same formula in the chart, but it might be too heavy for large data sets.
cheers,
Thanks Oleg..That's working fine.
One more small question. When i added that dimension to bar chart..iam getting 'SixMonthsBucket' values from Jan-2004 to Jan-2011. If i want to see only from Jan-2008 onwards...what will be the expression ?
I tried the below code in the expression but still getting same values from Jan-2004.
Code: If(SixMonthsBucket>'2008',SixMonthsBucket)
Many thanks
Hi,
Have you considered creating an independent Year-Month number using the autonumber function in your script.
In other words, create a line in your calendar script, such as:
autonumber(date(monthstart(Date), 'YYYYMM'), 99) as YMNum,
This orders all the dates in chronological order, due to the YYYYMM and then uses the autonumber to give each Year-Month value an independent ascending numerical value.
Then you can use set analysis against this number, such as:
sum({$<YMNum={$(=max(YMNum)), Year=, Month=, Day=}>} Sales) for the latest value and
sum({$<YMNum={$(=max(YMNum)-N)}, Year=, Month=, Day=>} Sales) for any year-month value N-months prior.
This technique allows you also to cross the Year divide line without the typical problems associated to making a selection on Year, e.g. if I use set analysis to go YMNum & YMNum-1, and my end user selects January of a Year, the 1st column will show Jan of the year and the 2nd column will show December of the previous year.
Hope it helps.
You can do it in a couple of ways... I usually prefer to alter the Expression and not to build complicated Calculated Dimensions - those had proven to be quite heavy.
What I'd do in your case is add a Set Analysis condition in your Expression - something like Year={">=2008"}. This will rule out any numbers prior to 2008