4 Replies Latest reply: Sep 26, 2013 11:16 PM by Julie Griffin RSS

    Show Monthly Actual or Budget Data based on Selected Date

    Julie Griffin

      Hi all,

       

      This is my first post so please forgive me if I get my terminology wrong.

       

      I am trying to create a P&L which has either the Actual or Budget figures depending on the Date Selected (See Below).

      Please note that I have had to set up the Max Date in this was as we have Forward Orders.

      Assume Date Selected is Sep 2013.

       

      Query 1:

      In this example, the calculated dimension works fine but when I apply the same logic to the Expression, it shows only the selected month. Is there a way to exclude the selected month in the If Statement? It's a little contradictory because I want it to acknowledge the selected month but then disregard it.

       

      Query 2:

      The Expression outlined above reports back the following:

      If the num(vSelectedMonth)<=num(vMaxDate) is True:

      The actual data will show for each month (in this case Jul, Aug, Sep) but all other fields are blank (Oct-Jun)

       

      If the num(vSelectedMonth)<=num(vMaxDate) is False:

      Every month will show the Budget Data.

       

      Would appreciate any assistance.

       

      Regards

      Julie

        • Re: Show Monthly Actual or Budget Data based on Selected Date
          Jonathan Dienst

          Julie

           

          I would approach thus problem by including this logic in my data model. By this I mean that I would combine Budget and Actual into a single fact table, and then have a field that indicates that the the number is budget or actual.

           

                    LOAD

                         ...

                         Month,

                         Actual,

                         0 As Budget,

                         'ACT' As Source,

                         Actual As ReportedAmount,

                         ...

                    FROM ActualData;

           

                    Concatenate

                    LOAD

                         ...

                         Month,

                        0 As Actual,

                         Budget,

                         'BUD' As Source,

                         Budget As ReportedAmount,

                         ...

                    FROM BudgetData;

                   

          This way, you can use ReportedAmount for the metric in your table, and the model will supply Actual or Budget according to the date.

           

          Regards

          Jonathan

           

          PS: I am assuming a data source in which the budget and actual sources don't overlap.

          • Re: Show Monthly Actual or Budget Data based on Selected Date

            Hi

             

            I have this functionality in one of my reports.  The key is to add the actuals and budgets and let the dimension of Month split out the result, i.e. Add the current and previous months actual data to all future budget via Set Analysis.  The expression would be as so;

             

            sum( { ' Month = {"<=$(= MAX([vSelectedMonth]) ) " } >} ' } GLSales)

            +

            sum( { ' Month = {">$(= MAX([vSelectedMonth]) ) " } >} ' } BudSales)

             

            I think this answers both questions, although please let me know if this is not the case.

             

            Let me know how you get on, if this is the correct answer or if it is merely helpful (or not).

             


            Regards


            Steve