Quarters for a line chart in a Calculated Dimension

[louwriet]

Hi,

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

Thank so much

Louw

[Gysbert_Wassenaar]

2014/03/01 could be evaluated as 2014 divided by 03 divided by 01 = 671.333

[louwriet]

Hi Gysbert,

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.

Thank you for your help.

Louw

[Gysbert_Wassenaar]

Can you post an example document that demonstrates the problem?

[louwriet]

Hi Gysbert.

Attached is qvw where i explain what i am trying to achieve

Thank you for your help

Louw

[Gysbert_Wassenaar]

See attached qvw.

[louwriet]

Hi Gysbert,

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.

Your help is really appreciated

Louw

[Gysbert_Wassenaar]

For rolling quarters you may want to read this document: Calculating rolling n-period totals, averages or other aggregations

For the Customer1 + top 2 of the rest... something like:

sum({<PRODUCT={'Customer1'}>+<PRODUCT={"=rank(sum({<PRODUCT-={'Customer1'}>}Units))<3"}>}Units)

[louwriet]

Hi Gysbert,

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 ?.

Thank so much

Louw

[Gysbert_Wassenaar · Accepted solution]

That needs a calculated dimension. Something like:

=If(PRODUCT='Customer1' or aggr(rank(sum({<PRODUCT-={'Customer1'}>}Units))<3,PRODUCT), PRODUCT, 'Others')