Can you please help me in resolving the below issues from the attached workbook. This is extension to the original request.
Global Filters: State, City & Year.
If year=2018-Q1 is selected then based on the Fact % value, it has to look up for year =2018 from lookup table. If year=2017-Q4 is selected then based on the Fact % value, it has to look up for year =2017 values from lookup table.
1. Sum for M1 & M2 are not correct after joining with Lookup table. Fact % value was derived from Sum(M1)/Sum(M2) at each ID level.
2. Also the logic to be implemented for few categories/ID's are different ex: 94: 0.04%. But when you look at the lookup table the min & max are reported in reverse order when compared with other ID's
Food- 0.04%: 3=7.065......7 + ((0.04-0.01)/(0.49-0.01))
Note: If the result of ((0.04-0.01)/(0.49-0.01)) >0.9 then it should display as 0.9 and not the actual result.
Ex: 7+0.987 =7.987. If we round to 1 decimal then it will be 8.0. but expected results are 7.9
3. For ID's=77, 48 & 61==> if SUM(M2) at ID level is 0 then 0 else 3 would suffice.
We have additional of 2 columns in lookup table for identifying the ID's which has min & max values in Asc/Desc order(R_Flag) and has single/multiple records(N_Flag) and see if it helps in implementing the logic bit easier.
ID's=94/48 has min & max values in reverse order, hence you see R_Flag=Y. Whereas ID=94 has classification in the lookup table and hence N_Flag=Y, but ID=48 doesn't have classification(single row) and hence you see as N_Flag=N.
ID=12/77/61 has min & max in Asc order, so R_Flag=N. Whereas ID's=77/61 doesn't have classification(single row) in the lookup table and hence N_Flag=N, but 12 has classification, so N_FLAG=Y.
If there is a single record for that ID, then we set the value as 3 by default.
4. For ID's=12,37,85 & 94==> If % of Fact Value falls in any of the below ranges then respective condition should be satisfied.
a) if SUM(M2) at ID level is 0 then 0.
b) if SUM(M2) at ID level is between 0 and 20 then 3.
c) if SUM(M2) at ID level is >20 then relevant logic should be applied for each ID's i.e. For 94, point #2 logic should be implemented and for ID's 12, 37, 85, the logic which you have provided holds good.
5. When Year=2018-Q1 is selected from quick filters(Fact table) then % of Fact value should be compared with 2018 from lookup table and when selected 2017-Q1 then % of Fact value should be calculated with year=2017.
I hope it is clear. Please let me know for any questions.
I have all these calculations implemented in a Function(Oracle). Created a calculated field with Live connection by using RAWSQLAGG_REAL but not matching exactly for some of the ID's or might have implemented it wrong. I believe extract will not work with these pass through results.Any thoughts on this approach?
SELECT ID, F_FUNCTION(ID,sum(M1),sum(M2),sum(Met),sum(notmet),'2018-Q1') as points from Table A
Note: % of Fact value will change based on State & City quick filter values. Based on the results, it should compare with Lookup table values for that time period.
I hope it is clear and let me know for any questions.
Attached updated workbook. Any help is highly appreciated.