Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gopikrishnach
Creator
Creator

Question on Pivot Table % Calculation

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.

45 Replies
sunny_talwar

Not sure I understand?

gopikrishnach
Creator
Creator
Author

Please check below screen shot. I don't show each Task 1 data as a separate row. I need to merge all Task 1 data in one row.

Checks.jpg

sunny_talwar

Best way to handle this would be to cleanse your data in the script using ApplyMap

Data Cleansing

gopikrishnach
Creator
Creator
Author

HI Sunny,

I have applied the same expression in my data, But  % values are not coming exactly for some task due to the SLA timing is considering properly. I have attached my actual data with task name, please refer Actual Data.xlxs and do the needful.

gopikrishnach
Creator
Creator
Author

iam using below expression for my tasks, but it is considering the SLA timings

=Num(Alt(Count(Aggr(If(([Reload Time])<=

Pick(Num(KeepChar([Task Name], '01234566789')), MakeTime(5), MakeTime(5), MakeTime(5,15), MakeTime(5,30)),

([Reload Time])), [Task Name], Date))/Count({<[Reload Time] -= {'-'}>}Date), 0), '#.%')

sunny_talwar

Can you load the data into a qvw file and show me the incorrect numbers please?

gopikrishnach
Creator
Creator
Author

Please check below screen shot, most of the Task % is showing as 0%.

Output.JPG

sunny_talwar

screenshot? How will I check the expression in screenshot?

gopikrishnach
Creator
Creator
Author

I used same expression, but i changed only Make time fields based upon the task names.

=Num(Alt(Count(Aggr(If(Max([Task Completion Time])<=

Pick(Num(KeepChar([Task Name], '0123456789')), MakeTime(5), MakeTime(5),MakeTime(5, 15),MakeTime(5, 30)),

Max([Task Completion Time])), [Task Name], Date))/Count({<[Task Completion Time] -= {'-'}>}Date), 0), '#.%')

sunny_talwar

Your task names changed from Task_1 to something else... you will need a mapping or something else to check which MakeTime() belongs to which task