10 Replies Latest reply: Jun 1, 2015 2:27 PM by Jonas Melo RSS

    Problem with comparing actual vs budget in bar graph

       

      Hi there,

       

      II may have missed it but I couldn’t find a solution for my need to have a bar graph that will compare actuals sales to budgeted sales.

       

      Here is the situation:

       

      I have a table with invoice information (invoice date, amount…). In the load process I have created Year, Month, YearMonth fields with no problem.

       

      I have a table with budget information. I only have Year, Month and amount as fields. I created a BudgetDate with MakeDate(YearBudget,MonthBudget,).

       

      I need to create a bar graph that will present invoice sum as well as budgeted sales f by YearMonth. Sounds easy…

       

      I have create the bar graph with sum of invoice by YearMonth with no problem. I use the dimension YearMonthInvoice and I have a measure Sum of sales which is Sum({$<[YearInvoiceDate]= >}[Job Contract Amount]) in order to present full data even if a year selection is done.

       

      I also have a measure Budgeted Sales which is causing problem.

       

      If I do sum({<YearBudget={'$(=max(YearInvoiceDate))'}>}MontantBudget), I get the same amount of budget for each month.

       

      If I do sum({<MonthYearBudget={'$(=max(MonthYearInvoice))'}>}MontantBudget), I don’t get any data for budget.

       

      I noticed that by changing my dimension from YearMonthInvoice to YearMonthBudget, the budget part is working fine but the actual sales goes wrong.

       

      I suspect I might have to create a relationship between Budget and Sales dates… or use a master calendar. But I also have many other dates (booking, delivery, closure…) which I may need to use to do comparison and I need to understand how to manipulate dates better.

       

      You help would be greatly appreciated. To help understanding the situation, I have attached the apps. Problem is in the first sheet named Global.

        Thanks in advance !

        • Re: Problem with comparing actual vs budget in bar graph
          Robert Hutchings

          You need a common YearMonth

          The way I do it is

           

          Concatenate Actual and budget in one table. (with a new say Type field as Budget and Actual)

           

          And then use the date field in the concatenated table to create and YearMth field.

          With Type = {Budget} in one expression and

          Type ={Actual} in another one

          • Re: Problem with comparing actual vs budget in bar graph
            Sunny Talwar

            What you need is a Link Table which would combine the two dates to a common calendar. Try something like this:

             

            Actual:

            LOAD RowNo() as ActualKey,

                      yourFields,

                      InvoiceDate

            FROM ....

             

            Budget:

            LOAD RowNo() as BudgetKey,

                      yourFields,

                      BudgetDate

            FROM ...

             

            LinkTable:

            LOAD ActualKey,

                      InvoiceDate as Date,

                      'Actual Date' as DateType

            Resident Actual;

             

            Concatenate (LinkTable)

            LOAD BudgetKey,

                      BudgetDate as Date,

                      'Budget Date' as DateType

            Resident Budget;

             

            Temp:

            LOAD Min(Date) as minDate,

              Max(Date) as maxDate

            Resident LinkTable;

             

             

            LET vMinDate = Num(Peek('minDate', 0, 'Temp'));

            LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));

            DROP Table Temp;

             

             

            TempCalendar:

            LOAD

              $(vMinDate) + IterNo() - 1 as Num,

              Date($(vMinDate) + IterNo() - 1) as TempDate

            AutoGenerate

              1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

             

             

            MasterCalendar:

            LOAD

              TempDate as Date,

              Week(TempDate) as Week,

              Year(TempDate) as Year,

              Month(TempDate) as Month,

              Day(TempDate) as Day,

              Weekday(TempDate) as WeekDay,

              'Q' & ceil(month(TempDate) / 3) as Quarter,

              'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) as QuarterYear,

              MonthName(TempDate) as MonthYear,

              Week(TempDate)&'-'&Year(TempDate) as WeekYear

            Resident TempCalendar

            Order By TempDate ASC;

             

            DROP Table TempCalendar;

             

            LET vMinDate = Null();

            LET vMaxDate = Null();

             

            You can find more details on the idea I used above in the following blog by Henric Cronström

            Canonical Date

             

            I hope this helps.

             

            Best,

            Sunny

              • Re: Problem with comparing actual vs budget in bar graph

                Hi Sunny and RJ for your prompt reaction.

                I tried RJ recommendation with concatenate the budget and the invoice date in a date table. I succeeded in creating the table but the bar graph still didn't work.

                 

                So I tried implementing Sunny solution. I succeeded in concatenate the BridgeDate table and create the MasterCalendar (at least I think...).

                But the bar graph is still not working... I don't have any bar showing up. The graph is just empty.

                I really dont understand because my Actual Sales is using the expression adviced in the post from HIC:

                Sum({<DateType = "Date facture">}[Job Contract Amount])

                And Budgeted Sales is: sum({<DateType = "Date Budget">}MontantBudget)

                 

                I'm really getting crazy with this... I can't find what I do wrong. Below is the part of my load script related to this problem, and the app.

                Your help would be greatly appreciated.

                Best regards,

                Patrick

                 

                LOAD
                    BudgetKey,
                    MakeDate(YearBudget,MonthBudget,) as BudgetDate,
                    MontantBudget
                FROM [lib://Offre Reporting/150529 AH Budget.xlsx]
                (ooxml, embedded labels, table is Budget);

                DateBridge:
                Load
                     ID_FACT,
                     'Date facture' as DateType,
                     InvoiceDate as BridgeDate
                Resident Facturation;
                 
                concatenate (DateBridge)
                Load
                     BudgetKey,
                     'Date Budget' as DateType,
                     BudgetDate as BridgeDate
                Resident Budget;

                Temp:
                LOAD Min(BridgeDate) as minDate,
                  Max(BridgeDate) as maxDate
                Resident DateBridge;


                LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
                LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
                DROP Table Temp;


                TempCalendar:
                LOAD
                  $(vMinDate) + IterNo() - 1 as Num,
                  Date($(vMinDate) + IterNo() - 1) as TempDate
                AutoGenerate
                  1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

                MasterCalendar:
                LOAD
                  TempDate as Date,
                  Year(TempDate) as Year,
                  Month(TempDate) as Month,
                  Day(TempDate) as Day,
                  MonthName(TempDate) as MonthYear,
                  Week(TempDate)&'-'&Year(TempDate) as WeekYear
                Resident TempCalendar
                Order By TempDate ASC;

                DROP Table TempCalendar;

                 

                LET vMinDate = Null();
                LET vMaxDate = Null();

                  • Re: Problem with comparing actual vs budget in bar graph
                    Sunny Talwar

                    Your master calendar should have the same date name as your DateBridge table. Right now they are not. They are two different names. I think once you make them the same, your chart should show up. (

                     

                    MasterCalendar:
                    LOAD
                      TempDate as BridgeDate Date, //Change Date to BridgeDate here...
                      Year(TempDate) as Year,
                      Month(TempDate) as Month,
                      Day(TempDate) as Day,
                      MonthName(TempDate) as MonthYear,
                      Week(TempDate)&'-'&Year(TempDate) as WeekYear
                    Resident TempCalendar
                    Order By TempDate ASC;

                    DROP Table TempCalendar;

                     

                    I hope this will help...

                     

                    Best,

                    Sunny

                      • Re: Problem with comparing actual vs budget in bar graph

                        Hi Sunny,

                        You were right about the fieldname error. I corrected it and now have the master calendar working fine.

                        But the bar graph didn't work in first instance... After many attemps, I found a way to get what I wanted to achieve. The expressions I used are:

                        Actual sales : Sum({$<[Year]= >}[Invoice Amount])

                        Budgeted sales : sum({$<[Year]= >}BudgetAmount)

                        With a dimension being YearMonth from the MasterCalendar.

                         

                        There might be other ways to get same result, but at least it is working for me.

                        Thanks a lot for you precious help Sunny!

                         

                        In case it would help someone else, here is the final loading script:

                        LOAD

                            ID_MISSION,

                            ID_FACT,

                            TYPE_FACT,

                            "Num Facture" as "Invoice Number",

                            "Intitulé Facture" as "Invoice Description",

                            Règlement as Paid,

                            "Date Règlement" as DatePaid,

                            "Date facture" as InvoiceDate,

                            Year("Date facture") as YearInvoiceDate,

                            Month("Date facture") as MonthInvoiceDate,

                            dual(Year("Date facture") & Month("Date facture"),Year("Date facture") & num(Month("Date facture"))) as YearMonthInvoice

                        FROM [lib://Offre Reporting/Base AH.xlsx] ooxml, embedded labels, table is Facturation);

                         

                        LOAD
                            BudgetKey,
                            MakeDate(YearBudget,MonthBudget,) as BudgetDate,
                            MontantBudget
                        FROM [lib://Offre Reporting/150529 AH Budget.xlsx] (ooxml, embedded labels, table is Budget);

                         

                        DateBridge:
                        Load
                             ID_FACT,
                             'Date facture' as DateType,
                             InvoiceDate as BridgeDate
                        Resident Facturation;
                         
                        concatenate (DateBridge)
                        Load
                             BudgetKey,
                             'Date Budget' as DateType,
                             BudgetDate as BridgeDate
                        Resident Budget;

                         

                        Temp:
                        LOAD Min(BridgeDate) as minDate,
                          Max(BridgeDate) as maxDate
                        Resident DateBridge;


                        LET vMinDate = Num(Peek('minDate', 0, 'Temp'));
                        LET vMaxDate = Num(Peek('maxDate', 0, 'Temp'));
                        DROP Table Temp;


                        TempCalendar:
                        LOAD
                          $(vMinDate) + IterNo() - 1 as Num,
                          Date($(vMinDate) + IterNo() - 1) as TempDate
                        AutoGenerate
                          1 While $(vMinDate) + IterNo() -1 <= $(vMaxDate);

                        MasterCalendar:
                        LOAD
                          TempDate as BridgeDate,
                          Year(TempDate) as Year,
                          Month(TempDate) as Month,
                          Day(TempDate) as Day,
                          dual(Year(TempDate) & Month(TempDate), Year(TempDate) & num(Month(TempDate))) as YearMonth
                        Resident TempCalendar
                        Order By TempDate ASC;

                        DROP Table TempCalendar;

                         

                        LET vMinDate = Null();
                        LET vMaxDate = Null();

                  • Re: Problem with comparing actual vs budget in bar graph
                    Robert Hutchings

                    Hi Patrick

                     

                    If you do it my way you need to concatenate the budget as follows

                     

                    I have a table with invoice information (invoice date, amount…). In the load process I have created Year, Month, YearMonth fields with no problem.

                     

                    I have a table with budget information. I only have Year, Month and amount as fields.


                    The budget must be loaded as

                    'Budget' as Type,  ///when loading invoice data add  'Actual' as Type

                    makedate (01, Month,Year) as Invoice Date,  //??or something like this to create a date in the correct format

                    MonthBudget as JobContractAmount,

                    monthYear (makedate (01, Month,Year)) as MonthYear



                    then do two formula's and one dimension


                    Dimension = MonthYear

                    Formula Actual ---> Sum ({<Type = {Actual}>}JobContractAmount

                    Formula Budget ---> Sum ({<Type = {Budget}>}JobContractAmount


                     

                      • Re: Problem with comparing actual vs budget in bar graph

                        Hi RJ,

                        Thanks a lot for your help. Now that I spent quite some time in getting Sunny solution working, I see better how to use your solution.

                        I feel yours would have been simpler, only using dates coming from actuals and from budget. I will keep this in mind for future needs.

                        But Sunny solution has advantages to provide with a master calendar, fulfilling with dates even if not present in actuals or budget. As you can see I did carefully my readings of Canonical Date post.

                        Thanks again for your help.

                        Best

                          • Re: Problem with comparing actual vs budget in bar graph
                            Robert Hutchings

                            That's great that you have got it to work

                             

                            You might like to also read this.

                             

                            Link table v Concatenate for fact tables

                             

                            It's personal preference but I tried both (link v concatenate) and eventually went the concatenate route unless there was a good reason not to (and there often is). In part because its easier to follow but also I tend to follow Henric's advice

                             

                            So budget I always concatenate but two large fact tables with totally different fields eg Logged a service request call with a entry date  and then doing the work for the call with a visit date. I would use a link table (canonical Date) and create a master calender

                             

                            Please remember to mark your question as answered. Even if you link it to your answer.

                        • Re: Problem with comparing actual vs budget in bar graph
                          Jonas Melo

                          Verify, if the link below help. Example in data model with tables Sales and Budget.

                           

                          http://qlikviewcookbook.com/2009/11/understanding-join-and-concatenate/