Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to create a Dashboard with Monthly % calculation by task, By monthwise and Yearwise based on Task Completion Time. I Created a Piovt Table with my data, but the % values are not coming correctly.
Here iam attaching the datewise date, based upon data we need to calculate the Monthwise reload completion % and yearwise reload completion % Value.
Please help me how to calculate the % calculation by Monthly and yearly for each task.
Please respond, this is my last concern. Iam attaching the QVW. Please review and do the needful
Thanks for the qvw.... I selected 1/1/2017, what is the issue here?
All of these guys are before 5, what is the issue?
No issue with the highlighted tasks, but some of the task were completed yesterday night (like 1_QVD_FINAL completion Time is 22:30:39 in the screen shot), which is yesterday's time and those % is not reflected in the chart. how we need to calculate SLA % for those Tasks.
Below are list of Tasks which are completed with yesterday's time.
1_ADR_finals |
1_CLASS QVD Extract |
1_DC_QVD Extract |
1_QVD_Final |
1_vd |
2_TVD |
2_Tvfb |
Just by looking at the time, how can you say it was completed today or yesterday? 22:30:39 can be from Today also? Unless you have date information in there, how can we differentiate?
Those timing are yesterday's and will trigger based upon the sql load, those load will complete daily in the evening/night, based upon the load completion, qlik task will automatically trigger. Ex: 1_QVD_Final data means (today-1)
What is the cutoff? Is it before 11:59 am it's today, anything after it yesterday?
yes, if it is before 11:59AM it's today. anything after it yesterday for those Tasks..
Okay, I wish you mentioned this part before
Try this
=Num(Alt(Count(Aggr(If(Max([Task Completion Time]) <= [SLA Timings] or Hour(Max([Task Completion Time])) >= 12, Max([Task Completion Time])), [Task Name], Date))/Count({<[Task Completion Time] -= {'x'}>}Date), 0), '#.%')
Above code is working Thank you very much.
But i have general question, if we have different SLA's for each month how to calculate the % values. Please guide me. i tried with flag, no luck. the completion timings, we are extracting from log files.
Like below
Task Name | Jun SLA Timings | Jul SLA Timings |
1_vd | 5:00:00 | 5:00:00 |
1_CURR | 5:00:00 | 5:00:00 |
1_ADR_finals | 5:00:00 | 5:00:00 |
1_ADR_prelims | 6:30:00 | 5:00:00 |
1_DC_QVD Extract | 5:00:00 | 5:00:00 |
1_CLASS QVD Extract | 5:00:00 | 5:00:00 |
1_BRDG_QVD_Extract | 5:00:00 | 5:00:00 |
1_SAL_QVD_Extract | 5:30:00 | 5:00:00 |
1_QVD_Final | 5:00:00 | 5:00:00 |
1_QUOT_Daily | 5:00:00 | 5:00:00 |
1_User_Pro | 5:00:00 | 5:00:00 |
2_TVD | 5:00:00 | 5:00:00 |
2_Tvfb | 5:00:00 | 5:00:00 |
3_APPBD | 6:08:00 | 6:08:00 |
3_APPBD_Trans | 6:30:00 | 5:15:00 |
4_APBD | 8:30:00 | 5:30:00 |
CrossTable this and join this to your Fact Table..... then you can create the flags in the script