Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I want to know if I can have really complex calculations in my load script. Right now I have a bar chart with multiple measures (Billing, Payment, Cost). I want the measures to be calculated in the load script and then all categorized as "Organizations" so I can have "Organizations" as a dimension (with Billing, Payment, and Cost being pieces of it). Someone made me a sample QVF like this and it's AMAZING, but they used single pieces of data for Billing, Payment, Cost (see below):
Organizations:
LOAD * Inline [
PermID, Organizations
2092totalcc, Billing
2092totallo, Payment
2093totalfield, Cost
]
;
Like I said, this works great and I'm able to do exactly what I want with the graph. However, instead of "2092totalcc" for Billing, I want what's written below.... I this possible? I could probably skip the year specification and just filter my year, but I'm lost on the rest of it. Any suggestions would be great! Thank you!
=(Avg( {1<Year = {$(=Max(Year))}>}
Aggr((
(Sum({1<PermID={'2095totalbill'
}>}Response)
))
/($(((Sum({1<PermID={'1001elecresi','1006gasoresi','1011wateresi','1016elecresi',
'1021elecresi','1026gaswresi','1031elecresi','1002eleccism','1007gasocism',
'1012watecism','1017eleccism','1022eleccism','1027gaswcism','1032eleccism',
'1003eleccila','1008gasocila','1013watecila','1018eleccila','1023eleccila',
'1028gaswcila','1033eleccila','1003elecothe','1008gasoothe','1013wateothe',
'1018elecothe','1023elecothe','1028gaswothe','1033elecothe'
}>}Response))
+(Sum({1<PermID={'1016elecresi','1021elecresi','1026gaswresi',
'1031elecresi','1017eleccism','1022eleccism','1027gaswcism','1032eleccism',
'1018eleccila','1023eleccila','1028gaswcila','1033eleccila','1018elecothe',
'1023elecothe','1028gaswothe','1033elecothe'
}>}Response))
+(Sum({1<PermID={'1031elecresi','1032eleccism','1033eleccila','1033elecothe'
}>}Response))
)))
,Company,Year)
))
Perhaps check this way?
Table_Name:
Load Company, Year, Response, PermID From Table;
LET vMaxYear = Peek('Year', 0, 'Table_Name');
Left Join (Table_Name)
Load Company, Year, Sum(Response) as All_Response Resident Table_Name Where Match(PermID, '1001elecresi','1006gasoresi','1011wateresi','1016elecresi',
'1021elecresi','1026gaswresi','1031elecresi','1002eleccism','1007gasocism',
'1012watecism','1017eleccism','1022eleccism','1027gaswcism','1032eleccism',
'1003eleccila','1008gasocila','1013watecila','1018eleccila','1023eleccila',
'1028gaswcila','1033eleccila','1003elecothe','1008gasoothe','1013wateothe',
'1018elecothe','1023elecothe','1028gaswothe','1033elecothe', '1016elecresi','1021elecresi','1026gaswresi',
'1031elecresi','1017eleccism','1022eleccism','1027gaswcism','1032eleccism',
'1018eleccila','1023eleccila','1028gaswcila','1033eleccila','1018elecothe',
'1023elecothe','1028gaswothe','1033elecothe', '1031elecresi','1032eleccism','1033eleccila','1033elecothe') Group By Company, Year;
Left Join (Table_Name)
Load Company, Year, Sum(Response)/Sum(All_Response) as Final_Response Resident Table_Name Where PremID='2095totalbill' Group By Company, Year;
Left Join (Table_Name)
Load Company, Year, Avg(Final_Response) as Avg_Final_Response Where Year='$(vMaxYear)' Group By Company, Year;
Amazing. I will try this, thank you!