Question on Pivot Table % Calculation

[gopikrishnach]

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.

[gopikrishnach]

Please respond, this is my last concern. Iam attaching the QVW. Please review and do the needful

[sunny_talwar]

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?

[gopikrishnach]

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

[sunny_talwar]

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?

[gopikrishnach]

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)

[sunny_talwar]

What is the cutoff? Is it before 11:59 am it's today, anything after it yesterday?

[gopikrishnach]

yes, if it is before 11:59AM it's today. anything after it yesterday for those Tasks..

[sunny_talwar]

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), '#.%')

[gopikrishnach]

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

[sunny_talwar]

CrossTable this and join this to your Fact Table..... then you can create the flags in the script