Hi there,
Please some one help me with calculation part that i want the respond count or performance of service no's based on CNT(Number of records). Everyday each service no has to respond 48 times and some service no's are responding more than 48. My requirement is: Day wise service no count percentage.(48=100%) More than 48 .... Month wise service no count percentage(48* Month ) if we select dc no:wdc0001 that dc have 38 service no's which i have shown in sheet in workbook. if we take dc wdc0001 as parameter then (38*48=1824) max records per day. i want percentage of count responded for dc on particular day. and month as well.if we select month December then (38*48*31=56,544) for wDc0001...max records. please find the attached workbook.
Thankyou,
suresh
Hi surya N,
Please follow below steps.
For Day Wise Count and %:
1) Create Calculated field which will count daily records:
SUM([Number of readings])*[Max No of Readings Per Day]
2) Now we will create % formula as follows
(SUM([Number of readings]) * [Max No of Readings Per Day])
/
([Total Meters ] * [Max No of Readings Per Day])
3) Plot this two measures on values
For Month wise count and %:
1) First we need to identify that what is max number of days in given month and year which can be achieve using below formula.
Number of Days
IF [Month] = 1 OR [Month] = 3 OR
[Month] = 5 OR [Month] = 7 OR
[Month] = 8 OR [Month] = 10 OR
[Month] = 12//(1 OR 3 OR 5 OR 7 OR 8 OR 10 OR 12)
THEN 31
ELSEIF [Month]= 2 AND [Year ] % 4 ==0
THEN 29
ELSEIF [Month]= 2 AND [Year ] % 4 <>0
THEN 28
ELSE 30
END
2) Now Create Calculated field which will use daily count and add max number of day for selected month:
MonthlyReading
{ INCLUDE MONTH([Interval TimeStamp]) : (
[DayReading]
) }*[Number of Days]
3) Now Calculate Max Monthly reading using below formula
MaxMonthlyReading
{ INCLUDE MONTH([Interval TimeStamp]) : SUM(
{ INCLUDE DAY([Interval TimeStamp]) : [Total Meters ]}
*
[Max No of Readings Per Day]
) }*[Number of Days]
4) Now we will calculate percentage based on above formulas:
% of Maximum readings per Month
[MonthlyReading]/[MaxMonthlyReading]
5) Now Plot those on chart as shown below:
I am attaching workbook as well for your reference. Let me know if you find any difficulties with my solution.
Thanks,
Dishant Shah
