% Calculation

[ha1]

Hi Everyone,

I have to calculate Approved rated based on 2 fields.

1. ActualDate, 2. Approved date.

Data:

AppID	ActualDate	ApprovedDate	Status	Cnt
1	9/10/2021	9/10/2021	Aprvd	1
2	9/10/2021	9/11/2021	Aprvd	1
3	9/10/2021	9/10/2021	Declnd	1
6	9/10/2021	9/10/2021	Declnd	1
4	9/11/2021	9/11/2021	Aprvd	1

Expected result:

ActualDate	Rate	Formula(Just for understanding)
9/10/2021	25%	(=1/4)….. only 1 application is approved on 10th out of 4 total applications, one application is approved on 11th so I haven't considered for 10th
9/11/2021	200%	(=2/1)….. 2 applications are approved on 11th over total applications(1), one application is created on 10th but approved on 11th

"ActualDate" field is  dimension.

Actual Approval Rate: Sum({<Status = {'Aprvd'}>}cnt)/sum(Cnt)

but in my requirement Approved count need to consider when  ActualDate = ApprovedDate

[sunny_talwar · Accepted solution]

The best way to handle this would be to create a Canonical Date in your script

[ha1]

looks interesting but creating multiple data sets is highly impossible here as data set huge with multiple dimensions, is there any other suggestions.

[ha1]

Any Suggestions please?

[sunny_talwar]

Were you able to resolve it or is this still pending?