Bar chart grouping

[#1]

Hi,

I have a bar chart that gives a total value by month.

Can someone please tell me how I can write an expression to show the last 12 months per month but group the prior months by year?

[#2]

Hi,

Use the function Year () and Month (). You can scale as the year and month.

Rebeca

[#3]

Hi Rebecaa,

Could you explain further?

In my chart at the moment I have the below formula, if I want to show individual months for the last 12 months OR group previosu 12 months into a year - how can I add this to this formula?

sum

(
If(Currency = 'GBP',[BILLINGSGBP FeesBilled Amt]+[BILLINGSGBP CostsBilled Amt],
If(Currency = 'USD',[BILLINGSUSD FeesBilled Amt]+[BILLINGSUSD CostsBilled Amt],
If(Currency = 'EUR',[BILLINGSEUR FeesBilled Amt]+[BILLINGSEUR CostsBilled Amt],
If(Currency = 'HKD',[BILLINGSHKD FeesBilled Amt]+[BILLINGSHKD CostsBilled Amt],
If(Currency = 'SGD',[BILLINGSSGD FeesBilled Amt]+[BILLINGSSGD CostsBilled Amt],
If(Currency = 'CHF',[BILLINGSCHF FeesBilled Amt]+[BILLINGSCHF CostsBilled Amt]

,'0')))))))

[#4]

You can send an example with data?

[#5]

I thought of one way I could do this, by creating a variable that picks up the max month from my data, then add this to my existing expression (below) then creating new expressions to give me the last 12 months etc, replacing the -1 with -2,-3 etc.

Could someone tell me if my approach seems ok to do or if there is a better standard practice way of doing this?

sum(

If(Currency = 'GBP' and penum=$(=vMaxPeriod)-1,[BILLINGSGBP FeesBilled Amt]+[BILLINGSGBP CostsBilled Amt],

If(Currency = 'USD' and penum=$(=vMaxPeriod)-1,[BILLINGSUSD FeesBilled Amt]+[BILLINGSUSD CostsBilled Amt],

If(Currency = 'EUR' and penum=$(=vMaxPeriod)-1,[BILLINGSEUR FeesBilled Amt]+[BILLINGSEUR CostsBilled Amt],

If(Currency = 'HKD' and penum=$(=vMaxPeriod)-1,[BILLINGSHKD FeesBilled Amt]+[BILLINGSHKD CostsBilled Amt],

If(Currency = 'SGD' and penum=$(=vMaxPeriod)-1,[BILLINGSSGD FeesBilled Amt]+[BILLINGSSGD CostsBilled Amt],

If(Currency = 'CHF' and penum=$(=vMaxPeriod)-1,[BILLINGSCHF FeesBilled Amt]+[BILLINGSCHF CostsBilled Amt],'0')))))))

[Anonymous]

Do have a Master Calendar table in your data model?

Could you add an "Bar Chart Date" field to that table and use it as the dimension on your chart? Some simple if/then logic and a left join would provide the field. Even if you don't have a Master Calendar table, I guess there's no reason why this fiel couldn't be added to you main data. Apologies if I've misunderstood your issue.

Eg.

Month	Barchart Date
01/01/2011	2011
01/02/2011	2011
01/03/2011	2011
01/04/2011	2011
01/05/2011	2011
01/06/2011	2011
01/07/2011	2011
01/08/2011	2011
01/09/2011	2011
01/10/2011	2011
01/11/2011	2011
01/12/2011	2011
01/01/2012	2012
01/02/2012	2012
01/03/2012	2012
01/04/2012	01/04/2012
01/05/2012	01/05/2012
01/06/2012	01/06/2012
01/07/2012	01/07/2012
01/08/2012	01/08/2012
01/09/2012	01/09/2012
01/10/2012	01/10/2012
01/11/2012	01/11/2012
01/12/2012	01/12/2012
01/01/2013	01/01/2013
01/02/2013	01/02/2013
01/03/2013	01/03/2013
01/04/2013	01/04/2013
01/05/2013	01/05/2013