Target Schedule

[erickd1190]

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?

[MK9885]

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.

[erickd1190]

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

[dineshraj]

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

[MK9885]

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?

[erickd1190]

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!!!

[erickd1190]

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?

[MK9885 · Accepted solution]

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.