Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Ceb
New Contributor III

QlikSense - Calculating Daily Targets based on a Yearly Target

Hi,

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

PROGRAMdateCASES
A4-Sept-193
A7-Sept-191
A8-Sept-191
A9-Sept-192
C1-Sept-192
C7-Sept-191
B4-Sept-191
B10-Sept-191
D5-Sept-191
D6-Sept-191
D12-Sept-191
E15-Sept-191
E18-Sept-191

 

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

3 Replies
Partner
Partner

Re: QlikSense - Calculating Daily Targets based on a Yearly Target

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
New Contributor III

Re: QlikSense - Calculating Daily Targets based on a Yearly 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

Partner
Partner

Re: QlikSense - Calculating Daily Targets based on a Yearly Target

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