5 Replies Latest reply: May 28, 2013 5:28 AM by Andrew Smith RSS

    Bar chart grouping

      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?

        • Re: Bar chart grouping

          Hi,

           

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

           

           

          Rebeca

          • Re: Bar chart grouping

            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')))))))

            • Re: Bar chart grouping

              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')))))))

              • Re: Bar chart grouping
                Andrew Smith

                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.

                 

                MonthBarchart Date
                01/01/20112011
                01/02/20112011
                01/03/20112011
                01/04/20112011
                01/05/20112011
                01/06/20112011
                01/07/20112011
                01/08/20112011
                01/09/20112011
                01/10/20112011
                01/11/20112011
                01/12/20112011
                01/01/20122012
                01/02/20122012
                01/03/20122012
                01/04/201201/04/2012
                01/05/201201/05/2012
                01/06/201201/06/2012
                01/07/201201/07/2012
                01/08/201201/08/2012
                01/09/201201/09/2012
                01/10/201201/10/2012
                01/11/201201/11/2012
                01/12/201201/12/2012
                01/01/201301/01/2013
                01/02/201301/02/2013
                01/03/201301/03/2013
                01/04/201301/04/2013
                01/05/201301/05/2013