Quarters for a line chart in a Calculated Dimension
Would really appreciate urgent help with a calculated dimension that i am stuck with.
I have 2 CALCULATED dimensions for a line chart - one customers(top 4 - this is working) and then quarter grouping backwards up to 12 - QTR1, QTR2, QTR3..........QTR12. This calcualted dimension is currently returning null value for the expression sum(sales)
I started testing with one quarter back wards
= IF ([TIME] >= $(vMinMonth1) and [TIME] >= $(vMaxMonth1) ,'daterange1', null() ) currently this is always returning null
my vMinMonth1 variable is working = monthstart( AddMonths(Max(LatestDate), -2))
my vMaxMonth1 variable is working = monthend(AddMonths(Max(LatestDate)))
when i debug is see the values pulling through
= IF([TIME]>= 2014/03/01 and [TIME]<= 2014/05/14 , 'daterange1',null() ) but its showing null values for the sales that happen during this time period
I am not with you what you are saying. Maybe i must explain a bit more what my problem is. The quarters that i want to create will be in a calulated dimension so that i can use that as x axis in chart.
my logic is : If date >= minmonth1 and date <= maxmonth1 then first point on axis QTR1
If date >= minmonth2 and date <= maxmonth2 then second point on axis QTR2 ............... UNTIL QTR12
the variables for minmonth and maxmonth is pulling in correct dates.
I don't know if the mistake is in my formula that i use or maybe the date format ? See attached excel where is used expressions to calsulate the sales for the qtrs but now in the line chart i need the quater daterange in x axis.
When i used your suggested calculated dimension for the quarters on my real data is saw that it is taking real quarters backwards - so April 14, May 14 - Q2 2014 and Jan 14 , Feb 14, March 14 - Q1 2014 - this is correct for real quarters but what i need is rolling quarters backwards starting with March 14, April 14, May14 - Q1 Dec 13, Jan 14, Feb 14 - Q2 ------up to 12 rolling quarters backwards. Is this possible ?
On the ranking - customer came to me today now and ask that Customer 1 must always show and the top two of the rest. Meaning in the ranking show Customer 1 and rank the rest and display top 2.
Thank you ! i got the rolling quarters working for my x axis - got the solution in the link that you refered to me - even got now the rolling 6 months working. Very helpfull link.
Your suggestion of expression works for me also, i just have one thing to overcome - all customers that falls outside of customer1 and the top 3 must be in a group 'others'. I need to show Customer1 the top 3 and then the rest in 'others'
Can one also work the 'others' in your suggested expression that is working now ?.