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]

Can you please provide the qvw file, it will help us.

[sunny_talwar]

End of my day today... see you next week

[gopikrishnach]

Hi Sunny, please provide the QVW file. It will help me.

[sunny_talwar]

Few things

1) You have been incrementally adding to your requirement, are you not sure what you want or you are scared to ask all at once? By not asking your really question, you are wasting both your and my time. To tell you the truth, I don't even know what you actually want, after we get this done, you will add another part to it and we will never get to what you want?

2) Do you want to give a try on your end to see if you can do what is needed? May be you can get it done with the hints I gave and learn something new today

3) If you are not able to get it done, I would help you one last time, but before I do, make sure you have put all your requirement here because after this, I am not going to waste both our time by incrementally helping you out.

Best,

Sunny

[gopikrishnach]

Sorry for troubling you. I will not add any requirement for this issue. Actually i want to display the end time based upon task and Date wise. Also i need completion time % per month wise. But my client is giving daily some modification, due to this iam adding the points here. I tried with flag,i assigned a flag values for current month in SLA Timings and join with my qvd( i don't have separate fact qvd), but no luck, because date and time is pulling from log file. Please help me, this is my last and final requirement. after this i will not add any thing. Thank you and sorry for troubling.

[sunny_talwar · Accepted solution]

Here you are

You provided the SLA Timings for only 2 months, that's why I removed all other data to show you how this will work for 2 months. But this can be expanded to more than 2 months.....

Table:

LOAD [Task Name],

    Date(Num#(Date)) as Date,

    [Task Completion Time],

    Month(Num#(Date)) as Month

FROM

(ooxml, embedded labels, table is Sheet2);

[SLA Time]:

CrossTable(Month, [SLA Timings])

LOAD * INLINE [

    Task Name, Jun, Jul

    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

];

[Final SLA Time]:

Right Keep (Table)

LOAD [Task Name],

Month(Date#(Month, 'MMM')) as Month,

[SLA Timings]

Resident [SLA Time];

DROP Table [SLA Time];

Once you have SLA Timings for all Months, you can remove the Right Keep in Red color above.....

I request that if you requirement is met now, please close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny