Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
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