topic Re: Re: Re: Create Achievement by Salesman in QlikView

Create Achievement by Salesman

Wed, 13 Aug 2014 09:26:02 GMT

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

Re: Create Achievement by Salesman

kaushiknsolanki — Wed, 13 Aug 2014 09:28:03 GMT

Hi,

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

Regards,

Kaushik Solanki

Re: Create Achievement by Salesman

Wed, 13 Aug 2014 09:59:49 GMT

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.

Re: Create Achievement by Salesman

kaushiknsolanki — Wed, 13 Aug 2014 10:02:04 GMT

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

Re: Re: Create Achievement by Salesman

Fri, 15 Aug 2014 03:23:33 GMT

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

Re: Re: Re: Create Achievement by Salesman

kaushiknsolanki — Fri, 15 Aug 2014 17:54:46 GMT

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

Re: Create Achievement by Salesman

Tue, 19 Aug 2014 07:44:46 GMT

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

Re: Create Achievement by Salesman

kaushiknsolanki — Sat, 23 Aug 2014 06:07:34 GMT

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

Re: Create Achievement by Salesman

Mon, 25 Aug 2014 04:31:34 GMT

Hi,

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

Regards

Sofyan B