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


    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?


          • 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


                3.Sales QTY

                4.Product Line



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



                      • Re: Budgeting
                        Robin Hausdörfer

                        off course, that's why the link table.





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


                        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)