QlikSense - Calculating Daily Targets based on a Yearly Target

[Ceb]

Hi,

I have a SQL table with Column Names Date,Program,Cases

PROGRAM	date	CASES
A	4-Sept-19	3
A	7-Sept-19	1
A	8-Sept-19	1
A	9-Sept-19	2
C	1-Sept-19	2
C	7-Sept-19	1
B	4-Sept-19	1
B	10-Sept-19	1
D	5-Sept-19	1
D	6-Sept-19	1
D	12-Sept-19	1
E	15-Sept-19	1
E	18-Sept-19	1

This table is updated daily.

I have Yearly targets for each Program. Also if required I can calculate the business working days for each month. Even Monthly

Next what I want to implement in my dashboard is using the Yearly Targets and the business working days, how can I calculate the Daily or Weekly Targets or Monthly(Currently I have monthly calculated manually and feeding it) for each Program?

I am not asking for a code but a procedure how to go about it would be helpful

Thanks

[StarinieriG]

Hi,

if you have business working days for all year and yearly target, you could calculate a daily target:

Yearly Target / Business working days for all year

Then, you could use this daily target to calculate monthly and weekly target:

Business working days for a week or for a month * Daily target

[Ceb]

Thanks for the reply!!

Assuming I have 2 tables

1) Program, Yearly Target

2)MonthName,Business Days

And of course the main table as mentioned in the question

How should I go about this? Is there a look up function in QlikSense which I could use. 

Also I feel the dates might create a problem as there could be holidays and of course weekends too would be there

[StarinieriG]

Try to follow this steps and let me know:

1) Table "2". Find year from field MonthName, then sum Business Days group by year, so you will have BusinessDaysYear

2) Join this table to Table "1". So you will have a new table with: Program, Yearly Target, BusinessDaysYear

3) Yearly  Target / Business Days = Daily Target

4) Then Load again Table "2" and join this Daily Target to this table. You could drop all the others.

5) Business Days * Daily Target let you know Monthly Target

Daily Target is a "general" value, so if you want to know if today it's 0 or not, you have to create a calendar with all dates and for each, a flag for holidays / weekend.

For example you could load all holidays in a table, then concat them into a variable called Holidays and use it as If(Match(data, $(Holidays))>0,1,0) as flag.

Daily Target * flag will be automatically 0 if today it's not a working day