7 Replies Latest reply: Mar 28, 2017 11:36 AM by Aehman K RSS

    Target Schedule

    Erick Dameron

      HI All,

       

      I am working on a running analysis of Actual vs Target by representative.

      What I need to do is link my Calendar to my Target Schedule so that if a user selects the month of September they will be able to see the actual(Sum(Sales) vs the target(Daily Rate * Working days) for each Department and staff.

       

       

       

      Anyone have any ideas?

        • Re: Target Schedule
          Aehman K

          If you've Date field in your Fact, you can link it to Master Calendar.

          Usually it is by DateID

          Maybe you can use the below in both Master Calendar and your Fact...

           

          Master Calendar:

          Load...

          trim(date(TempDate,'YYYYMMDD'))as [DateID],

          ......

           

           

          Fact Table:

           

          trim(date(YourDateFieldHere,'YYYYMMDD'))as [DateID],

           

          Note: Tempdate is a field you'll pre define in your Master Table

          Something like this...

           

          TempCalendar:

              LOAD

                             $(varMinDate) + Iterno()-1 as Num,

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

                             AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

           

          and the variables in Temp table are also predefined, basically it's a Master Calendar.

          • Re: Target Schedule
            Erick Dameron

            I thought about doing that but wouldn't the numbers be inaccurate? I can have multiple staff complete orders on the same day and have the same daily target....

              • Re: Target Schedule
                Aehman K

                No, as long as the dates from your Fact or Data base are matching the dates from Master.

                If you like you can test it with small sample data if the dates are matching between those tables and also data related to those dates?

                  • Re: Target Schedule
                    Erick Dameron

                    Even with multiple records per date in my fact table?

                     

                    The issue I am having now is that when I link my Target table to my Fact table it creates multiple lines per staff.

                     

                     

                    Would it be better to create another master calendar for each position and the relevant daily target?

                      • Re: Target Schedule
                        Aehman K

                        It won't matter if a single employee has 1 recorded date or 100.

                        And same for multiple records for single date.

                        If 10 fields have same date, each field will show it's related date (which can be same date or different date) but if the fields change the date also changed.

                         

                        Ex:

                        Field1 Field2 Field3 Field4 Date

                        ABC, BCD, CDE, EFG, 03-26-2017

                        BBB,ABD,XYZ,QWE, 03-26-2017

                         

                        So if you take only Field1 and Date field, you'll get

                        Field1, Date

                        ABC, 03-26-2017

                        BBB, 03-26-2017

                         

                        Field2, Date

                        BCD, 03-26-2017

                        ABD, 03-26-2017

                         

                        Each record will have it's own date as the value is different in fields.

                        But if you consider it vise versa, Single date will be linked to all the values.

                        Maybe I'm wrong but cannot say for sure unless you can provide with the sample data and the result you looking for.

                        Thanks.

                  • Re: Target Schedule
                    dineshraj ramesh

                    Hi Erick,

                     

                    You want to link target dates to master Calendar ?

                     

                    If I am Correct refer the link.

                     

                    https://community.qlik.com/blogs/qlikviewdesignblog/2014/02/17/canonical-date

                      • Re: Target Schedule
                        Erick Dameron

                        Not necessarily link target dates. Our reps will have multiple transactions in a day, so at the basic level what I need to do is be able to do is create a table in Qlik Sense that allows me to list staff, their position, their current sales, and their cumulative target based on the number of work days(i.e Month of March).

                         

                        Currently my Target table is set up like below:

                        Position:          Daily Target

                        Sales Rep             100

                        Sr Sales Rep         300

                        Support                 150

                        Technician             200

                         

                        I will mention that the post you suggested did help with another piece of what I am working on, so thank you!!!