Max(Quarter) not working. Quarter = 2001-Q1, 2001-Q2, etc. Help!

[#1]

Hello,

EDIT - I was able to partially solve this question, now the issue is specifically with sorting by Quarter: for vCalc = 2, Max(Quarter) does not seem to work at all. Quarter is loaded as Year(Date#(YEAR,'YYYY')) & '-Q' & ceil(Month(Date#(MONTH,'MMM'))/3) as Quarter

In my line chart I need to sort the legend (brand) by a ascending market share, but only for the last selected date.

This is a bit more complicated, because both chart dimensions and expressions are variables, which change based buttons. The chart shows market share by brand over time. Specifically, the x-axis is a variable for time, that can be either Year (e.g. 2004), Quarter (e.g. Q1-2004), or Date (e.g. Sep 2004). The expression is written to include annual market share, quarterly market share, monthly market share, and rolling 12, 6 and 3 month share.

Expression below, where "vCalc" is a variable that changes based on which button you press: (This expression works fine.)

If($(vCalc) = 1, sum(Units)/sum(Total <Year> {<[Truck Brand], Group = >} Units),

If($(vCalc) = 2, sum(Units)/sum(Total <Quarter> {<[Truck Brand], Group = >} Units),

If($(vCalc) = 3, sum(Units)/sum(Total <Date> {<[Truck Brand], Group = >} Units),

If($(vCalc) =4, aggr(RangeSum(Above(Sum({$<Year>} Units),0,12)), $(v_sortSelected),Date)/aggr(RangeSum(Above(Sum(Total <Date> {$<Year, [Truck Brand], Group = >} Units),0,12)), $(v_sortSelected), Date),

If($(vCalc) =5, aggr(RangeSum(Above(Sum({$<Year>} Units),0,6)), $(v_sortSelected),Date)/aggr(RangeSum(Above(Sum(Total <Date> {$<Year, [Truck Brand], Group = >} Units),0,6)), $(v_sortSelected), Date),

If($(vCalc) =6, aggr(RangeSum(Above(Sum({$<Year>} Units),0,3)), $(v_sortSelected),Date)/aggr(RangeSum(Above(Sum(Total <Date> {$<Year, [Truck Brand], Group = >} Units),0,3)), $(v_sortSelected), Date)))))))

Now, I want to sort the legend based on highest units sold for each brand, but only for the last selected time period. For example, if 2005 - 2012 were selected, then it would arrange the legend for most units sold in 2012. To do this, in the sort tab of my chart, I entered the expression:

If($(vCalc) = 1, sum( {<Year = {'$(=Max(Year))'} >} Units),

If($(vCalc) = 2, sum( {<Quarter = {'$(=Max(Quarter))'} >} Units),

If($(vCalc) = 3, sum( {<Date = {'$(=Max(Date))'} >} Units),

If($(vCalc) =4, aggr(RangeSum(Above(Sum({$<Year>} {<Date = {'$(=Max(Date))'} >}Units),0,12)), $(v_sortSelected),Date),

If($(vCalc) =5, aggr(RangeSum(Above(Sum({$<Year>} {<Date = {'$(=Max(Date))'} >}Units),0,6)), $(v_sortSelected),Date),

If($(vCalc) =6, aggr(RangeSum(Above(Sum({$<Year>} {<Date = {'$(=Max(Date))'} >}Units),0,3)), $(v_sortSelected),Date)))))))

vCalc = 1 and vCalc = 3 work okay, but the others are not working correctly when trying to sort by the latest date. This leaves me with two problems:

1. (SOLVED) For the rolling months, vCalc = 4,5,6, I am not sure where to write in the Max(Date) function so that it only calculates the rolling (n) months for the latest date selected.
2. And for vCalc = 2, Max(Quarter) does not seem to work at all. Quarter is loaded as Year(Date#(YEAR,'YYYY')) & '-Q' & ceil(Month(Date#(MONTH,'MMM'))/3) asQuarter

Thanks for any help!

[JonnyPoole]

Maybe sort quarter by expression:

left(Quarter,4) * 10  +  right(Quarter,1)

then its ripe for a numeric ascending sort.

2010-Q1 ->  20101

2010-Q2 ->  20102

2010-Q3 ->  20103

2010-Q4 ->  20104

2011-Q1 ->  20111

2011-Q2 ->  20112

2011-Q3 ->  20113

2011-Q4 ->  20114

[#3]

Thanks Jonathan! I think that is the direction I need to go. How would I plug that expression in to my sort expression? It must be part of my full sort expression, because it is based on the variable vCalc. The Quarter section below:

if($(vCalc) = 2, sum( {<Quarter = {'$(=Max(Quarter))'} >} Units),

Should I do something like:

if($(vCalc) = 2, sum( {<Quarter = {'$(=Max(left(Quarter,4)*10 + right(Quarter,1))'} >} Units),

I am not sure how to consider that in my expression.

[JonnyPoole · Accepted solution]

To get both sides of the equation to be the same it would almost be something like the following , but I would i'm definitely not certain this would work unless i could test it on a sample first.

if($(vCalc) = 2, sum(

{<Quarter =

               {'$(=

                    left(  Max(left(Quarter,4)*10 + right(Quarter,1)) ,4)

                    & '-Q' & 

                    right(Max(left(Quarter,4)*10 + right(Quarter,1)),1)

                 )'} >} Units),

[#5]

Thanks! That worked for me. The only thing I had to do was change the single quotes to double quotes after sum({Quarter= { and after (Quarter,1)),1)):

If($(vCalc) = 2, sum( {<Quarter = {"$(=left(Max(left(Quarter,4)*10 + right(Quarter,1)),4)&'-Q'&right(Max(Left(Quarter,4)*10 + right(Quarter,1)),1))"} >} Units)