Calculate YTD Quota

[#1]

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

Reps

Product

Apr

May

YTD Actual

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

[Gysbert_Wassenaar]

Can you post a qlikview document that demonstrates the problem?

[#3]

Can you explain little bit and share the content

[#4]

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