sumOver continues to be unintuitive to use. Why?

Zach · February 24, 2022, 7:25pm

It would be great if sumOver were easier to use.

I need the functionality of the POST_AGG_FILTER combined with PRE_AGG and it works with sheet controls.

I can get the maths to work properly when using POST_AGG_FILTER but that requires the partioned data points be included in the visual. I don’t want those data points on the visuals. Ultimately I need to create a map with the resulting values.

PRE_AGG seems like it would work, but the values don’t sum properly. The result is totals that are way off base and not even close.

Come on team.

sumOver(sum(Enrolled),[County,State],POST_AGG_FILTER)

sumOver(Enrolled,[County,State],PRE_AGG)

darcoli · February 24, 2022, 9:57pm

Could you check if this gives you what you need:

sumOver(
  min(
    sumOver(Enrolled,[County,State],PRE_AGG)
  ),
  [County,State],
  POST_AGG_FILTER
)

?

Zach · February 24, 2022, 10:15pm

Thanks for the effort darcoli.

Unfortunately using the POST_AGG_FILTER means the County and State fields have to be present in the visual and they won’t be.

I did try your suggestion and got the message that the Table calculation attribute reference(s) are missing in field wells.
Adding County and State to the visual does result in accurate numbers, but I don’t need/want County/State in the visual.

-Zach

darcoli · February 24, 2022, 10:31pm

So you want an overall unpartitioned sum. Does it work if you remove those two fields from the partitioning in the outer function

sumOver(
  min(
    sumOver(Enrolled,[County,State],PRE_AGG)
  ),
  [],
  POST_AGG_FILTER
)

Zach · February 25, 2022, 4:33pm

I do need the sum partitioned by County and State behind the scenes. The end goal is a calculated field that determines a %. The formula needs to work on a map, where County and State Fields will be present, and on a table, where County and State fields will NOT be present). The intent is to have the map click filter the table below to only show the Carrier(s) in the State and County that was clicked on with the appropriate %.

I realize I’ve only give you a few pieces of the puzzle. I’ll lay out the rest of it.

I have columns County State, Contract ID, Organization Name, Organization Type, Plan Type, Enrolled, File, Carrier

Numerator = Sum of Enrolled Partitioned by Carrier, State, County. The achieve this, I was intending on putting the Carrier field in the Size By field well of the map. State and County fields in the Geospatial field wells.
Denominator = sumOver Enrolled, partitioned by State and County

The result is a % which represents that carriers % of Members enrolled in the county.

I intend on placing a Filter Control on the sheet for Carrier, State, and County

Data Sample

County	State	Contract ID	Organization Name	Organization Type	Plan Type	Enrolled	File	Carrier
Los Angeles	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	293196	2/1/2022	KAISER F
Los Angeles	CA	H5425	SCAN HEALTH PLAN	Local CCP	HMO/HMOPOS	106054	2/1/2022	SCAN HEA
San Diego	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	99146	2/1/2022	KAISER F
Sacramento	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	94949	2/1/2022	KAISER F
Alameda	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	92848	2/1/2022	KAISER F
Clark	NV	H0609	UnitedHealthcare Benefits of Texas Inc	Local CCP	HMO/HMOPOS	77521	2/1/2022	UnitedHe
Contra Costa	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	77487	2/1/2022	KAISER F
Maricopa	AZ	H0609	UnitedHealthcare Benefits of Texas Inc	Local CCP	HMO/HMOPOS	76723	2/1/2022	UnitedHe
Santa Clara	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	75971	2/1/2022	KAISER F
Orange	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	75311	2/1/2022	KAISER F
Los Angeles	CA	H0543	UHC OF CALIFORNIA	Local CCP	HMO/HMOPOS	74986	2/1/2022	UHC OF C
Tarrant	TX	H4590	UNITEDHEALTHCARE BENEFITS OF TEXAS INC	Local CCP	HMO/HMOPOS	73329	2/1/2022	UNITEDHE
Cook	IL	H1468	HUMANA BENEFIT PLAN OF ILLINOIS INC	Local CCP	HMO/HMOPOS	72534	2/1/2022	HUMANA B
Riverside	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	71293	2/1/2022	KAISER F
Bexar	TX	H4590	UNITEDHEALTHCARE BENEFITS OF TEXAS INC	Local CCP	HMO/HMOPOS	70086	2/1/2022	UNITEDHE
San Bernardino	CA	H0524	KAISER FOUNDATION HP INC	Local CCP	HMO/HMOPOS	67762	2/1/2022	KAISER F
Dallas	TX	H4590	UNITEDHEALTHCARE BENEFITS OF TEXAS INC	Local CCP	HMO/HMOPOS	65142	2/1/2022	UNITEDHE
Broward	FL	H1036	HUMANA MEDICAL PLAN INC	Local CCP	HMO/HMOPOS	63163	2/1/2022	HUMANA M
San Diego	CA	H0543	UHC OF CALIFORNIA	Local CCP	HMO/HMOPOS	62892	2/1/2022	UHC OF C
Wayne	MI	H9572	BCBS OF MICHIGAN MUTUAL INSURANCE COMPANY	Local CCP	Local PPO	62783	2/1/2022	BCBS OF 
Oakland	MI	H9572	BCBS OF MICHIGAN MUTUAL INSURANCE COMPANY	Local CCP	Local PPO	58382	2/1/2022	BCBS OF 
Orange	CA	H5425	SCAN HEALTH PLAN	Local CCP	HMO/HMOPOS	58096	2/1/2022	SCAN HEA
Bronx	NY	H3359	HEALTHFIRST HEALTH PLAN INC	Local CCP	HMO/HMOPOS	56431	2/1/2022	HEALTHFI
Kings	NY	H3359	HEALTHFIRST HEALTH PLAN INC	Local CCP	HMO/HMOPOS	53931	2/1/2022	HEALTHFI
Monroe	NY	H3351	EXCELLUS HEALTH PLAN INC	Local CCP	HMO/HMOPOS	50311	2/1/2022	EXCELLUS

Karthik_Tharmarajan · February 25, 2022, 7:21pm

Hi, Just to understand the requirement here, I have put together a sample chart. Are you expecting something similar to what I have below.
Map is filtered for LA county and on the right we have the split by carrier

Below is the tabular view including County and State and on the right side table, data is filtered for LA county.

darcoli · February 27, 2022, 8:43pm

Thanks for the additional info @Zach I think the field you need (called Carrier %) can be defined as follows:

100 * sumOver(Enrolled, [County,State, Carrier], PRE_AGG)
/
sumOver(Enrolled, [County,State], PRE_AGG)

You can then use this inside a maps visual to show the percentage of enrolled by Carrier within each State and County as follows:

You could also use the same field within a table and you can set up a filter action from the first table visual to this filter this table (for just the County field), as follows:

Zach · February 28, 2022, 6:40pm

You have it exactly right.

Karthik_Tharmarajan · February 28, 2022, 8:23pm

Hi, For below chart to work, I created a calculation called Percent with logic as :

sumOver(Enrolled,[Carrier,State,County],PRE_AGG)/ sumover(Enrolled,[State,County],PRE_AGG)

Chart is filtered for LA.

Please be aware that when we don’t make any selection from the map then the table view on the right will have data aggregated at carrier level across county/state which may be misleading.