7 Replies Latest reply: Nov 3, 2017 8:21 AM by Angel Tomov RSS

    Budgeting

    Angel Tomov

      Hello guys, I have got the following problem.

       

      I have 2 tables
      1 for budgets

      1 for sales .

       

      I am using a Combo chart with Months as dimensions and Sales as expression.

       

      I have included second expression which is Sum(budget).

       

      But whenever I select for example Q3, it shows me all the items, which I have sold for the period and I want it to show the budget even for those items, that I havent sold.

       

      For example if my budget for august is 10 dollars for 10 items, that gives a total of 100

      But if I have actually sold only 9 items, my budget is 90 dollars. ( how can I show 100 dollars budget here)

       

      I was thinking of using some kind of Set analysis, but couldn't do it.

       

      Any suggestions?

        • Re: Budgeting
          Pratyush Shastri

          Hi Could you provide the structure for the two tables?


          Regards
          Pratyush

          • Re: Budgeting
            Andrea Gigliotti

            or even better the qvf file with sample data.

              • Re: Budgeting
                Angel Tomov

                I am working through RDP, on my client's server in Austria, and I can't send anything from it.

                 

                I am using my personal computer for communications here. Sorry..

                 

                My sales data is from a datawarehouse and has around 150 columns
                but from them I use around 40, my table is named Sales

                 

                 

                 

                 

                My budget data is from excel file and i have I

                1. Item Number

                2.Description

                3.Sales QTY

                4.Product Line

                5.Year

                6.Month

                7.Price CHF

                8. Budget value

                 

                 

                All the Fieldnames from the excel file, are present in the Sales table,
                so i was wondering if Concat is possible, but Im not sure if it's not going to disrupt my other Data..

              • Re: Budgeting
                Robin Hausdörfer

                just create a link table with all common dimensions of budget and sales...

                and connect a calendar with the link table date field.

                  • Re: Budgeting
                    Angel Tomov

                    Yes, but I want to show all the budgeted items,
                    Not only the budgeted Items, that I have sold , but all
                    i have used a set expression, but it' doesn't work as intended...

                     

                    Sum({$<YEAR={2017}>+1<[Country]-{"Bulgaria"}>}Budget)

                      • Re: Budgeting
                        Robin Hausdörfer

                        off course, that's why the link table.

                         

                        example:

                         

                        LINKTABLE:

                        LOAD distinct COMMONFIELD1, COMMONFIELD2, ... COMMONFIELD_N from BUDGETTABLE;

                        concatenate

                        LOAD distinct COMMONFIELD1, COMMONFIELD2, ... COMMONFIELD_N from SALESTABLE;

                         

                        If you don't won't a "synthetic" Link Table, then you could use autonumber()...

                    • Re: Budgeting
                      Angel Tomov

                      I have done what I was looking for with the following expression

                       

                      IN my bar chart i have Month as dimension and Sales as expression 1

                       

                      expression 2 is Sum({1}Budget)