Create Achievement by Salesman

[#1]

Hi there, I want to Create Dashboard to show achievement on Sales.

I have two table first one is fact sales and the second is for target.

I have a problem when showing achievement sales by target in one year the total target don't summarize value that has no correspondence month on fact table. So if there is no record in fact table for the month of April the target value also don't summarize the target for April. I want the total target summarize all the month including for the month that has no records on fact table.

Regards

Sofyan B

[kaushiknsolanki]

Hi,

Instead of taking month from Fact, take month, year from target table.

Regards,

Kaushik Solanki

[#3]

I wrote an expression like this to calculate sales achievement

Sum({<year={$(=Max(year))}>} Sales_Value) / Sum({<year={$(=Max(year))}>} target)

like i said previously the summary of target has some value missing from the month that has no record on the fact table.

[kaushiknsolanki]

Hi,

If your target table has values for all months, then there shouldnt be a problem.

Kindly attach sample QVW file which replicated the issue.

Regards,

Kaushik Solanki

[#5]

Hi Kaushik

Thanks for your reply, I attached the qvw file, as you can see at file for the month of April I have target bu no data on fact table. when I summarize the target the month of April is exclude from the total.

I want all target is summarize even there is no data on fact_table for that month.

Regards

Sofyan B

[kaushiknsolanki]

Hi,

Have a look at the attached file.

Hope this will clear that from where you need to pick up Month and Year.

Regards,

Kaushik Solanki

[#7]

Hi Kaushik

Sorry for my late response.

Is that mean if i have several salesman, so every salesman must have the same data on the cal table that you created.

Regards

Sofyan B

[kaushiknsolanki · Accepted solution]

Hi,

It depends, but as per the understanding, your target table should always have record for each salesperson for each month. If that is the case then always take the month year from target table, else you will have to create a link table which will be a combination of both the table with Date and Salesperson as field. then you will have to take month from this link table, instead of individual tables.

Regards,

Kaushik Solanki

[#9]

Hi,

Thanks for your answer. I got the idea form your answer and it's work

Regards

Sofyan B