3 Replies Latest reply: Jun 1, 2015 4:12 PM by Rajashree Reddy RSS

    Calculate YTD Quota

    Rajashree Reddy

      Hi,

      I need help in creating a chart for monthly performance of  reps.

      They have a 'product quota' for each product for the year (ex: Product A 300,000,Product B 400,000...)

      and  'Monthly Target':(Like 7% for April & 8% in May ..) same for all products either A or B

         

      RepsProductAprMayYTD ActualYTD Quota%Achieved

       

       

      YTD Actual:-  sum of total sales  April & May

      YTD Quota -: Total Product quota for the year * Monthly Target.

      On selection of the billing month April, the report should display April actuals  & YTD quota as Product Quota * 7%

      On selection of the billing month May, the report should display May actuals and YTD quota as Product Quota *8%

      When no billing month is selected , sum of April & May actuals and YTD quota as Product Quota *( 7%+8% )

       

      There is a list of 10 products and not all products sell every month. However the YTD quota for all the products  should be available.

       

      My problem is  YTD Quota shows only for products that are sold . and I am using this..

      YTD Quota = Sum( product Quota) * Monthly Target * Count(distinct Month)

      Could you please help.

        • Re: Calculate YTD Quota
          Gysbert Wassenaar

          Can you post a qlikview document that demonstrates the problem?

          • Re: Calculate YTD Quota
            Sudheer Kumar Mukka

            Can you explain little bit and share the content

              • Re: Calculate YTD Quota
                Rajashree Reddy

                Sorry for the late response. Here is an example.

                Month/Rep/Product  is the key here.

                The Target table X shows  target for all three products for months Apr & May.

                From Sales table Y,  we see that rep S2 did not sell  product B in  May.

                However , I would like to show his Target for that month & that product in the chart.

                 

                Target X:
                Month, Sales_Rep, Product, Target
                Apr,    S1,        A,       1000    
                Apr,    S1,        B,       2000
                Apr,    S1,        C,       3000
                May,    S2,        A,       1500
                May,    S2,        B,       2500
                May,    S2,        C,       2000

                 

                Sales Y:
                Month, Sales_Rep, Product,   Sales
                Apr,    S1,        A,         1k    
                Apr,    S1,        B,         2k
                Apr,    S1,        C,         3k
                May,    S2,        A,         1.5k
                May,    S2,        C,         2.0k